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!
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());
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'),
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.
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
Source: Stackoverflow.com