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