Search code examples
oracle-sqldeveloper

Oracle giving different times after changing timezone and converting to char

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.

Solution

  • 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