String to date in Oracle with milliseconds

Question

I want to convert the follow string to date   2004-09-30 23 53 48 140000000   I tried   to date       YYYY-MM-DD HH24 MI SS FF9     But PL SQL keep throwing this error   ORA-01821  date format not recognized    FF9 is incorrect for Oracle  any suggestion

User · Answer

I don t think you can use fractional seconds with to date or the DATE type in Oracle   I think you need to timestamp which returns a TIMESTAMP type

User · Answer

Oracle stores only the fractions up to second in a DATE field   Use TIMESTAMP instead   SELECT  TO TIMESTAMP  2004-09-30 23 53 48 140000000    YYYY-MM-DD HH24 MI SS FF9   FROM    dual     possibly casting it to a DATE then   SELECT  CAST TO TIMESTAMP  2004-09-30 23 53 48 140000000    YYYY-MM-DD HH24 MI SS FF9   AS DATE  FROM    dual

[oracle] String to date in Oracle with milliseconds

Examples related to oracle

Examples related to to-date