如何转换“1985-02-07T00:00:00.000Z"(ISO8601) 到 Oracle 中的日期值?

2022-01-13 00:00:00 timestamp date oracle

我尝试将时间戳 ("1985-02-07T00:00:00.000Z") 转换为日期,但我多次尝试均未成功.

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.

以下是我尝试过的查询:

Below is the query I have tried:

 select to_date('1985-02-07T00:00:00.000Z', 'YYYY-MM-DDTHH24:MI:SS.fffZ')
 from dual;

非常感谢您的建议.

推荐答案

to_date 将输入转换为不支持小数秒的 DATE 类型.要使用小数秒,您需要使用在使用 to_timestamp

to_date converts the input to a DATE type which does not support fractional seconds. To use fractional seconds you need to use a TIMESTAMP type which is created when using to_timestamp

pst 关于 ff3 修饰符的评论也是正确的.

pst's comment about the ff3 modifier is also correct.

格式掩码中的常量"值需要用双引号括起来

"Constant" values in the format mask need to be enclosed in double quote

所以最后的陈述是:

select to_timestamp('1985-02-07T00:00:00.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"')
from dual;

相关文章