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?

possible duplicate of String to date in Oracle with milliseconds — Igby Largeman
– Igby Largeman, Commented Feb 7, 2012 at 16:47

Justin Cave · Accepted Answer · 2012-02-07 16:46:10Z

128

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' )

answered Feb 7, 2012 at 16:46

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark J. Bobak · Accepted Answer · 2012-02-07 16:46:36Z

17

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.

answered Feb 7, 2012 at 16:46

Mark J. Bobak

14.5k6 gold badges45 silver badges73 bronze badges

Comments

Baum mit Augen · Accepted Answer · 2018-11-18 21:11:14Z

10

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'),

edited Nov 18, 2018 at 21:11

Baum mit Augen♦

50.4k25 gold badges153 silver badges187 bronze badges

answered Nov 18, 2018 at 21:01

SANKAR

1251 silver badge2 bronze badges

1 Comment

brewmanz Over a year ago

cough 'six digits millisecond' should probably be '6 digits microsecond'

joym8 · Accepted Answer · 2020-11-30 19:59:50Z

9

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

edited Nov 30, 2020 at 19:59

joym8

4,2604 gold badges56 silver badges100 bronze badges

answered Apr 25, 2017 at 9:15

dipti

1151 silver badge2 bronze badges

Comments

Ishani Gupta · Accepted Answer · 2016-02-24 08:32:55Z

-11

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());

answered Feb 24, 2016 at 8:32

Ishani Gupta

72 bronze badges

1 Comment

Chris K Over a year ago

What does Java have to do with Oracle PL/SQL?

Collectives™ on Stack Overflow

Using Oracle to_date function for date string with milliseconds

5 Answers 5

Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related