Rafael Ferreira Rocha Rafael Ferreira Rocha - 3 years ago 147
SQL Question

What is the format for '2015-10-01T12:00:00.000-03:00' in Oracle

I wanna convert '2015-10-01T12:00:00.000-03:00' varchar into date in Oracle, like this:

select
to_date('2010-12-14:09:56:53', 'YYYY-MM-DD:HH24:MI:SS') -
to_date('2010-12-14:09:56:46', 'YYYY-MM-DD:HH24:MI:SS')
from dual;


but for that format.

Please if the question is unclear, say me why and I'll give more information, don't negative me I'm noob :).
Obs: I am using Oracle Database 11g Enterprise Edition

MT0 MT0
Answer Source

Use TO_TIMESTAMP_TZ and if you want to convert it to a date then you can convert it to one time zone (typically UTC) and then CAST it to a date:

SELECT CAST(
         TO_TIMESTAMP_TZ(
           '2015-10-01T12:00:00.000-03:00',
           'YYYY-MM-DD"T"HH24:MI:SS.FF3TZR'
         )
         AT TIME ZONE 'UTC'
         AS DATE
       )
FROM   DUAL;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download