TO_DATE function is not working with timezone info.

Question

I am trying below :

SELECT TO_CHAR( TO_DATE('Wed May 18 00:00:00 IST 2016', 'DY MON DD HH24:MI:SS TZR YYYY'),'DD-MM-YYYY') FROM DUAL;

and expecting 18-05-2016

But it says - 01821. 00000 - "date format not recognized"

It works well if I remove IST and corresponding TZR.

What's going wrong ?

Answer 1

The Oracle DATE data type does not have a time zone component. Neither does a plain TIMESTAMP. You need to convert to a TIMESTEMP WITH TIME ZONE:

SELECT TO_CHAR(TO_TIMESTAMP_TZ('Wed May 18 00:00:00 IST 2016',
  'DY MON DD HH24:MI:SS TZR YYYY', 'NLS_DATE_LANGUAGE=ENGLISH'),
  'DD-MM-YYYY')
FROM DUAL;

But IST is not a recognised time zone region, so that will still get ORA-01882: timezone region not found. You may have to explicitly replace that with a known region, e.g.:

SELECT TO_CHAR(TO_TIMESTAMP_TZ(REPLACE('Wed May 18 00:00:00 IST 2016',
  'IST', 'Asia/Calcutta'), 'DY MON DD HH24:MI:SS TZR YYYY', 'NLS_DATE_LANGUAGE=ENGLISH'),
  'DD-MM-YYYY')
FROM DUAL;

TO_CHAR(TO
----------
18-05-2016

Alternatively, if you won't be doing any manipulation on it and want the IST year anyway, you can extract the date components directly from the string, or treat IST as a fixed value and not try to interpret it at all:

SELECT TO_CHAR(TO_DATE('Wed May 18 00:00:00 IST 2016',
  'DY MON DD HH24:MI:SS "IST" YYYY', 'NLS_DATE_LANGUAGE=ENGLISH'),
  'DD-MM-YYYY')
FROM DUAL;

TO_CHAR(TO
----------
18-05-2016

... but that may not be suitable for your actual situation.