3

I am trying to obtain a DATE object with the following statement:

TO_DATE('1970-01-01 00:00:00 UTC','YYYY-MM-DD HH24:MI:SS TZR')

and

TO_DATE('1970-01-01 00:00:00 UTC','YYYY-MM-DD TZH:TZM:SS TZR')

But in both cases, it is telling me: ORA-01821: date format not recognized.

I got the TZR format specifier from https://www.techonthenet.com/oracle/functions/to_date.php, namely:

TZD     Daylight savings information. For example, 'PST'
TZH     Time zone hour.
TZM     Time zone minute.
TZR     Time zone region.

What is the correct way to specify a date/time with a time-zone?

6

Datetime Format Models

TZR - Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in DATE formats.

select to_timestamp_tz
  (
    '1970-01-01 00:00:00 UTC',
    'YYYY-MM-DD HH24:MI:SS TZR'
  )
  as result
from dual;

RESULT
----------------------------------------
01-JAN-70 12.00.00.000000000 AM UTC

If you want a DATE type, you need to convert it:

select cast(to_timestamp_tz
  (
    '1970-01-01 00:00:00 UTC',
    'YYYY-MM-DD HH24:MI:SS TZR'
  ) as date)
  as result
from dual;

RESULT
----------------------------------------
01-JAN-70
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.