Using Oracle to_date function for date string with milliseconds
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!
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' )