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

What mask are you supplying to TO_DATE()?

cagcowboy
– cagcowboy

2009-05-05 14:38:31 +00:00
Commented May 5, 2009 at 14:38 — cagcowboy
– cagcowboy, Commented May 5, 2009 at 14:38

Dave Costa · Accepted Answer · 2009-05-05 14:44:53Z

8

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.

answered May 5, 2009 at 14:44

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Gareth Over a year ago

Dave, this is brilliant cheers - I cant use a TIMESTAMP_WITH_TIME_ZONE datatype though as its not my database. However, that would be the preferred solution. Cheers

jason44107 Over a year ago

Thank you for this, Dave. The double quotes around the "T" was the magic incantation I needed to get the conversion to work. :)

Quassnoi · Accepted Answer · 2009-05-05 14:43:50Z

6

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

answered May 5, 2009 at 14:43

Quassnoi

428k94 gold badges629 silver badges624 bronze badges

Comments

Michał Niklas · Accepted Answer · 2009-05-06 08:30:21Z

1

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

answered May 6, 2009 at 8:30

Michał Niklas

54.6k19 gold badges79 silver badges125 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related