如何在 Oracle 中将“1985-02-07T00:00:00.000Z”（ISO8601）转换为日期值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8654292/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to convert "1985-02-07T00:00:00.000Z" (ISO8601) to a date value in Oracle?
提问by Abilash
I tried to convert a time-stamp ("1985-02-07T00:00:00.000Z") to a date and I failed to succeed in my several different attempts.
我试图将时间戳（“1985-02-07T00:00:00.000Z”）转换为日期，但在几次不同的尝试中都没有成功。
Below is the query I have tried:
以下是我尝试过的查询：
select to_date('1985-02-07T00:00:00.000Z', 'YYYY-MM-DDTHH24:MI:SS.fffZ')
from dual;
Your suggestions are greatly appreciated.
非常感谢您的建议。
回答by a_horse_with_no_name
to_dateconverts the input to a DATEtype which does not support fractional seconds. To use fractional seconds you need to use a TIMESTAMPtype which is created when using to_timestamp
to_date将输入转换为DATE不支持小数秒的类型。要使用小数秒，您需要使用TIMESTAMP在使用时创建的类型to_timestamp
pst's comment about the ff3modifier is also correct.
pst 关于ff3修饰符的评论也是正确的。
"Constant" values in the format mask need to be enclosed in double quote
格式掩码中的“常量”值需要用双引号括起来
So the final statement is:
所以最后的声明是：
select to_timestamp('1985-02-07T00:00:00.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"')
from dual;
回答by Rob van Wijk
SQL> select cast(to_timestamp('1985-02-07T00:00:00.000Z', 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"') as date)
2 from dual
3 /
CAST(TO_TIMESTAMP('
-------------------
07-02-1985 00:00:00
1 row selected.
回答by u1048564
SELECT to_timestamp_tz('2012-08-08T09:06:14.000-07:00','YYYY-MM-DD"T"HH24:MI:SS.FF3TZR')
FROM dual;
External table DDL,
extract_date char(29) DATE_FORMAT timestamp WITH TIMEZONE mask 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZR'
回答by álvaro González
Some rules to follow:
- Literals must be double-quoted:
MMexpects a month number,"MM"expects a double-M. - The format for fractional seconds is
FF, notForFFF. You specify the number of digits with a trailing integer, e.g.FF3. - But dates cannot hold fractional seconds anyway so you cannot use
FF3in this context.
- 文字必须用双引号引起来：
MM期望是月份数，"MM"期望是双 M。 - 小数秒的格式是
FF, notForFFF。您可以使用尾随整数指定位数，例如FF3。 - 但是日期无论如何都不能保留小数秒，因此您不能
FF3在这种情况下使用。
This works:
这有效：
SELECT TO_DATE('1985-02-07T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS')
FROM dual;
I don't know if there's a way to ignore fractional seconds in TO_DATE()so I've used string manipulation functions to strip them out:
我不知道是否有办法忽略小数秒，TO_DATE()所以我使用字符串操作函数将它们去掉：
SELECT TO_DATE(SUBSTR('1985-02-07T00:00:00.000Z', 1, 19), 'YYYY-MM-DD"T"HH24:MI:SS')
FROM dual;
回答by user4524067
if u want to get the string in datetime format then try this....
如果您想以日期时间格式获取字符串，请尝试此操作....
select to_char(TO_DATE('2012-06-26T00:00:00.809Z', 'YYYY-MM-DD"T"HH24:MI:SS".""ZZZZ"'),'yyyy-MM-dd hh:mm:ss PM') as EVENT_DATE from dual
EVENT_DATE
-----------------------
2012-06-26 12:06:00 AM
only for date simply use...
仅用于日期，只需使用...
select TO_DATE('2012-01-06T00:00:00.809Z', 'YYYY-MM-DD"T"HH24:MI:SS".""ZZZZ"') from dual
