Convert ISO-8601 date string to oracle time format
I have an ISO-8601 date in VARCHAR2 type, how can I convert this String date to timestamp in oracle db?
Date example: "2014-09-12T11: 53: 06 + 00: 00"
It might be something like the following, but I'm not sure what the format is.
SELECT to_timestamp_tz ('2014-09-12T11:53:06+00:00', ????) FROM DUAL
+3
source to share
1 answer
Elements of the date format model specified in the 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
Fixed T
can be included as a character literal:
You can include these characters in your date format:
- Punctuation marks such as hyphens, forward slashes, commas, periods, and colons
- Double-quoted character literals
TZH
is the time zone of the zone, and TZM
is the minutes of the time zone. The rest are more common model elements.
+8
source to share