我必须执行一些插入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' )更多推荐
发布评论