Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

String to date in Oracle with milliseconds

Tags:

oracle

to-date

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?

like image 615
Custodio Avatar asked Nov 18 '09 18:11

Custodio


People also ask

Does Oracle date have milliseconds?

Our application needs to get a date value including millisecond in PL/SQL packages,but the date datatype in Oracle doesn't support millisecond.

What is To_char and To_date in Oracle?

To_char formats a DATE into a string using the given format mask. To_date converts a STRING into a date using the format mask. Your client then displays that date using a format mask (set at session/instance level).

What is FF in Oracle TIMESTAMP?

Instead, you need to use the TIMESTAMP datatype instead of the DATE datatype. The TIMESTAMP datatype has fractional seconds, as noted by the FF notation: 'DD-Mon-YYYY HH24:MI:SS.FF'


2 Answers

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 
like image 100
Quassnoi Avatar answered Sep 17 '22 16:09

Quassnoi


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.

like image 42
Jeremy Bourque Avatar answered Sep 19 '22 16:09

Jeremy Bourque