I have trying to convert the UTC time to local time in Oracle Developer .I needed it in a particular format after conversion but after conversion to character the time comes out to be completely different.
Use TO_CHAR
:
TO_CHAR(
from_tz (cast(e.reg_dt_tm as timestamp),'UTC') at time zone 'Australia/Sydney',
'YYYY-MM-DD hh24:mm:ss.ff TZR'
)
Or change the default TIMESTAMP_TZ
format in SQL Developer:
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR';
and then run the query.
db<>fiddle here