CODEWITHSUNDEEP
oracle-databaseto-date
Custodio
Custodio

Reputation: 8934

String to date in Oracle with milliseconds

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?

Upvotes: 31

Views: 184661

Answers (2)

Jeremy Bourque
Jeremy Bourque

Reputation: 3543

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.

Upvotes: 5

Quassnoi
Quassnoi

Reputation: 425291

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

Upvotes: 75

Related Questions