Tutorial :Oracle Date formatting “2009-02-13T11:46:40+00:00”



Question:

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


Solution: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  


Solution: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.


Solution: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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »