Using Oracle to date function for date string with milliseconds

Question

I have to perform some inserts into an Oracle DB  I have some dates  in the following format   23 12 2011 13 01 001    Following the documentation I wrote inserts to date as follows   to date  23 12 2011 13 01 01    DD MM YYYY HH24 MI SS     which works properly  Now I have dates with milliseconds with the format   23 12 2011 13 01 001    I ve tried the following   to date  23 12 2011 13 01 001    DD MM YYYY HH24 MI SSFF3     which is incorrect  delivers an error 01821  00000 -   date format not recognized     Which  String  should I use for this format with milliseconds   Thanks in advance

User · Accepted Answer

An Oracle DATE does not store times with more precision than a second   You cannot store millisecond precision data in a DATE column   Your two options are to either truncate the string of the milliseconds before converting it into a DATE  i e    to date  substr  23 12 2011 13 01 001   1  19    DD MM YYYY HH24 MI SS      or to convert the string into a TIMESTAMP that does support millisecond precision  to timestamp   23 12 2011 13 01 001    DD MM YYYY HH24 MI SSFF3

User · Answer

You have to change date class to timestamp   String s df format c getTime     java util Date parsedUtilDate   df parse s     java sql Timestamp timestamp   new java sql Timestamp parsedUtilDate getTime

User · Answer

For three digits millisecond   TO CHAR LN AUTOD UWRG DTTM  MM DD YYYY HH24 MI SS FF3     For six digits millisecond   TO CHAR LN AUTOD UWRG DTTM  MM DD YYYY HH24 MI SS FF

User · Answer

TO DATE supports conversion to DATE datatype  which doesn t support milliseconds   If you want millisecond support in Oracle  you should look at TIMESTAMP datatype and TO TIMESTAMP function   Hope that helps

User · Answer

You can try this format SS FF for milliseconds  to timestamp table 1 date col  DD-Mon-RR HH24 MI SS FF   For more details  https   docs oracle com cd B19306 01 server 102 b14200 functions193 htm

[oracle] Using Oracle to_date function for date string with milliseconds

Examples related to oracle

Examples related to to-date