Oracle Date formatting "2009-02-13T11:46:40+00:00"

Question

Hi Guys,

I've had some brilliant help before and I'm hoping you can get me out of a hole again.

I've got a date coming in from a web service in this format: 2009-02-13T11:46:40+00:00

which to me looks like standard UTC format.

I need to insert it into an Oracle database, so I'm using to_date() on the insert. Problem is, I cant get a matching formatting string for it and keep getting "ORA-01861: literal does not match format string" errors.

I know its a fairly trivial problem but for some reason I cannot get it to accept the right format string. Any help appreciated.

Thanks :)

Gareth

Answer 1

SELECT  CAST(TO_TIMESTAMP_TZ(REPLACE('2009-02-13T11:46:40+00:00', 'T', ''), 'YYYY-MM-DD HH:MI:SS TZH:TZM') AS DATE)
FROM    dual

Answer 2

You can directly convert it to a TIMESTAMP_WITH_TIME_ZONE datatype.

select
  to_timestamp_tz('2009-02-13T11:46:40+00:00','YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')
from
 dual

TO_TIMESTAMP_TZ('2009-02-13T11:46:40+00:00','YYYY-MM-DD"T"HH24:MI:SSTZH:TZM
---------------------------------------------------------------------------
13-FEB-09 11.46.40.000000000 AM +00:00

(I'm assuming the input string is using a 24-hour clock since there is no AM/PM indicator.)

If you want to convert that to a simple DATE, you can, but it will lose the time zone information.

Answer 3

To import date in specified format you can set nls_date_format.

Example:

alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS'

This way your SQL statements can be shorter (no casts). For various mask look at Datetime Format Models