Convert String ISO-8601 date to oracle's timestamp datatype

database oracle iso8601
hades picture hades · Oct 31, 2014 · Viewed 19.8k times · Source

I have a ISO-8601 date in VARCHAR2 type, how can i convert that String date to timestamp in oracle db?

Date Example: "2014-09-12T11:53:06+00:00"

Maybe is something like the following but i not sure what is the format.

SELECT to_timestamp_tz ('2014-09-12T11:53:06+00:00', ????) FROM DUAL

Answer

Alex Poole picture Alex Poole · Oct 31, 2014

The date format model elements are listed in the Datetime Format Models documentation:

SELECT to_timestamp_tz ('2014-09-12T11:53:06+00:00', 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')
FROM DUAL

TO_TIMESTAMP_TZ('2014-09-12T11:53:06+00:00','YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')
---------------------------------------------------------------------------
12-SEP-14 11.53.06.000000000 +00:00

The fixed T can be included as a character literal:

You can include these characters in a date format model:

  • Punctuation such as hyphens, slashes, commas, periods, and colons
  • Character literals, enclosed in double quotation marks

TZH is tome zone hour, and TZM is time zone minutes. The rest are more common model elements.

Related questions

How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
How can I get column names from a table in Oracle?

I need to query the database to get the column names, not to be confused with data in the table. For example, if I have a table named EVENT_LOG that contains eventID, eventType, eventDesc, and eventTime, then I would …

Read More