ORA-01821: date format not recognized error for IS

Question

I am trying to convert the date in SQL based on the parameter value in my Java code. However when the below query is executed I am getting error . Request you to help me in fixing this query.

 SELECT TO_DATE ('2015-08-26T05:46:30.488+0100',
 'YYYY-MM-DD"T"hh24:mi:ss.sTZH:TZM')
  FROM DUAL
  *
Error at line 2
ORA-01821: date format not recognized

Date and Time format info:

http://www.w3.org/TR/NOTE-datetime

Answer 1

You have two issues: TO_DATE doesn't recognise any time zone components or fractional seconds, you'll have to convert it to a timestamp with tome zone; and .s isn't how you represent fractional seconds anyway, you need .ff. The valid format models are shown in the documentation.

Putting those together you can do:

SELECT TO_TIMESTAMP_TZ ('2015-08-26T05:46:30.488+0100',
 'YYYY-MM-DD"T"hh24:mi:ss.ffTZHTZM')
FROM DUAL;

TO_TIMESTAMP_TZ('2015-08-26T05:46:30.488+0100','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZ
-------------------------------------------------------------------------------
26-AUG-15 05.46.30.488000000 +01:00                                             

If you really want it as a date you'll need to decide what to do with the time zone information - either assume it's local time (essentially ignore it) or convert to UTC, or some other time zone. You may really want to keep it as a timestamp with time zone though.

Answer 2

Well, the error message is pretty specific. Oracle does not recognize the given date format YYYY-MM-DD"T"hh24:mi:ss.sTZH:TZM.

You can refer to this page in order to build a proper date format: https://www.techonthenet.com/oracle/functions/to_date.php