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

问题描述

我必须对Oracle DB进行一些插入.我有一些约会 格式如下

I have to perform some inserts into an Oracle DB. I have some dates in the following format

'23.12.2011 13:01:001'

在我编写的文档之后，插入 to_date 如下:

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'

我尝试了以下操作:

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

这是不正确的(传递错误01821.00000-无法识别日期格式".)

which is incorrect (delivers an error 01821. 00000 - "date format not recognized").

该格式应使用哪个字符串"(以毫秒为单位)?

Which "String" should I use for this format with milliseconds?

提前谢谢！

推荐答案

Oracle DATE所存储的时间精度不超过一秒.您不能在DATE列中存储毫秒精度数据.

An Oracle DATE does not store times with more precision than a second. You cannot store millisecond precision data in a DATE column.

您的两个选择是截断毫秒数的字符串，然后将其转换为DATE，即

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

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

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

这篇关于对日期字符串使用Oracle to_date函数(以毫秒为单位)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！