ORA-01821: date format not recognized error for ISO 8601 date with local time

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 timestamp with time 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.