CODEWITHSUNDEEP
oracle-database
ray
ray

Reputation: 4250

Oracle - how to convert datetime to TZ format?

I have this time format:

2020-09-08 14:00:00

and I want to convert it to following which is from Sydney time to UTC:

2020-09-08T01:00:00Z

Upvotes: 0

Views: 911

Answers (1)

Popeye
Popeye

Reputation: 35920

First of all, giving the answer considering that your data is actual varchar2:

SQL> SELECT TO_CHAR(CAST(TO_DATE('2020-09-08 14:00:00','YYYY-MM-DD HH24:MI:SS')
  2     AS TIMESTAMP) AT TIME ZONE 'Australia/Sydney',
  3    'YYYY-MM-DD"T"HH24:MI:SS"Z"')
  4    FROM DUAL;

TO_CHAR(CAST(TO_DATE
--------------------
2020-09-08T18:30:00Z

SQL>

Please note that You can also set NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD"T"HH24:MI:SS"Z"' in your session (and don't use the TO_CHAR in your query) as per your requirement. Also, you can use UTC timezone instead of Australia/Sydney if you want to convert your local date to UTC timezone. DB<>Fiddle for UTC conversion.

If the given data is already in date datatype stored in your table, then you just need to use CAST as follows: (TO_DATE is not required)

SELECT TO_CHAR(CAST(YOUR_DATE_COLUMN
                   AS TIMESTAMP) AT TIME ZONE 'Australia/Sydney', 
              'YYYY-MM-DD"T"HH24:MI:SS"Z"')
  FROM YOUR_TABLE;

Upvotes: 2

Related Questions