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

Question

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

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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;

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

Sign up