How can I correctly use the timezone format specifier in a TO_DATE call in Oracle 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?
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