使用Oracle to

使用Oracle to_date以毫秒为单位的日期字符串函数(Using Oracle to_date function for date string with milliseconds)

我必须执行一些插入Oracle数据库。 我有以下格式的日期

'23.12.2011 13:01:001'

按照以下文档，我写入了插入to_date ：

to_date('23.12.2011 13:01:01', 'DD.MM.YYYY HH24:MI:SS')

哪些工作正常。 现在我的格式为毫秒

'23.12.2011 13:01:001'

我试过以下内容：

to_date('23.12.2011 13:01:001', 'DD.MM.YYYY HH24:MI:SSFF3')

这是不正确的（提供错误01821. 00000 - “日期格式无法识别”）。

我应该使用哪种“字符串”用于毫秒级的格式？

提前致谢！

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!

最满意答案

Oracle DATE不会以比一秒更高的精度存储时间。 您不能在DATE列中存储毫秒精度数据。

您的两个选项是在将其转换为DATE之前截断毫秒的字符串，即

to_date( substr('23.12.2011 13:01:001', 1, 19), 'DD.MM.YYYY HH24:MI:SS' )

或者将字符串转换为支持毫秒精度的TIMESTAMP

to_timestamp( '23.12.2011 13:01:001', 'DD.MM.YYYY HH24:MI:SSFF3' )

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