Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

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

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...