0

I am not able to obtain the date object from date string 2019-01-21T19:02:25Z

select to_char(to_date('2019-01-21T19:02:25Z','yyyy-mm-ddThh24:mi:ssZ'),'dd/mm/yyyy hh24:mi:ss') from dual;

yields

ORA-01821: date format not recognized 01821. 00000 - "date format not recognized" *Cause:
*Action:

May I know what date format is used.

MT0
  • 143,790
  • 11
  • 59
  • 117
Devanshu Kashyap
  • 185
  • 1
  • 22
  • 1
    Possible duplicate of [How to convert "1985-02-07T00:00:00.000Z" (ISO8601) to a date value in Oracle?](https://stackoverflow.com/questions/8654292/how-to-convert-1985-02-07t000000-000z-iso8601-to-a-date-value-in-oracle) This is DEFINITELY oracle. `ORA-12345` is the giveaway. – O. Jones Jan 23 '19 at 12:03

1 Answers1

5

Either use quotes to match the T and Z as literals:

SELECT TO_CHAR(
         TO_DATE(
           '2019-01-21T19:02:25Z',
            'yyyy-mm-dd"T"hh24:mi:ss"Z"'
         ),
         'dd/mm/yyyy hh24:mi:ss'
       )
FROM   DUAL;

or, match the T as a literal and use TO_TIMESTAMP_TZ with the TZH and TZM format models to match the time zone hours and minutes components (or, instead, TZR to match the time zone region):

SELECT TO_CHAR(
         TO_TIMESTAMP_TZ(
           '2019-01-21T19:02:25Z',
            'yyyy-mm-dd"T"hh24:mi:ssTZHTZM'
         ),
         'dd/mm/yyyy hh24:mi:ss'
       )
FROM   DUAL;

db<>fiddle

MT0
  • 143,790
  • 11
  • 59
  • 117
  • 1
    Is it somewhere documented or is it a hidden feature that `"Z"` is recognized as shortcut for `+00:00`, resp. `UTC`? – Wernfried Domscheit Jan 23 '19 at 14:04
  • @WernfriedDomscheit Z is short for [Zulu time](https://www.timeanddate.com/time/zones/z) which is the military equivalent of UTC. – MT0 Jan 23 '19 at 14:07
  • 1
    Yes, I know "Z" means "Zulu time" - I am just not aware that Oracle would know this also. At least timezone `Z` is not listed in `V_$TIMEZONE_NAMES` – Wernfried Domscheit Jan 23 '19 at 14:10
  • @WernfriedDomscheit That would be a great thing to ask in a separate question as you may get other people who are able to give you an answer (it isn't mentioned in the Format Models documentation). – MT0 Jan 23 '19 at 14:18