mysql t16 00 00.000z,如何转换“1985-02-07T00：00：00.000Z” （ISO8601）到Oracle中的日期值？...

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;

Your suggestions are greatly appreciated.

解决方案

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's comment about the ff3 modifier is also correct.

"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;