pasaba por aqui pasaba por aqui - 11 days ago 6
Java Question

Java Date.toString in Oracle's TO_DATE

The default format for Date.toString() seems to be, in an US locale, something like:

Thu Nov 24 15:20:52 CET 2016


Oracle database has a function "TO_DATE" that allows convert from string to date. First argument is the date format.

Which is the correct date format to map a Java Date.toString() string? Is it:

DAY MONTH DD HH24:MI:SS TZD YYYY


?

Thanks.

MT0 MT0
Answer

An Oracle DATE data type does not have a time zone - you need a TIMESTAMP WITH TIMEZONE data type:

SELECT TO_TIMESTAMP_TZ(
         'Thu Nov 24 15:20:52 CET 2016',
         'DY MON DD HH24:MI:SS TZR YYYY'
       )
FROM DUAL

If you want to convert to a DATE (and the time zone region is always CET) then you can use:

SELECT TO_DATE(
         'Thu Nov 24 15:20:52 CET 2016',
         'DY MON DD HH24:MI:SS "CET" YYYY'
       )
FROM DUAL

If you want it as a DATE data type and to respect the time zone in the original string then you will need to (1) convert it to a TIMESTAMP WITH TIMEZONE data type; (2) convert that value to a standardized time zone (UTC is often used for this); then (3) convert that to a date:

SELECT CAST(
         TO_TIMESTAMP_TZ(
           'Thu Nov 24 15:20:52 CET 2016',
           'DY MON DD HH24:MI:SS TZR YYYY'
         )
         AT TIME ZONE 'UTC'
         AS DATE
       )
FROM   DUAL;

Which will output the date 2016-11-24 14:20:52 (the UTC representation of the input date).