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