将 Oracle to_date 函数用于以毫秒为单位的日期字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9180014/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Oracle to_date function for date string with milliseconds
提问by Luixv
I have to perform some inserts into an Oracle DB. I have some dates in the following format
我必须对 Oracle DB 执行一些插入操作。我有以下格式的一些日期
'23.12.2011 13:01:001'
Following the documentation I wrote inserts to_dateas 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").
这是不正确的（提供错误 01821. 00000 - “无法识别日期格式”）。
Which "String" should I use for this format with milliseconds?
对于这种格式，我应该使用哪个“字符串”以毫秒为单位？
Thanks in advance!
提前致谢！
回答by Justin Cave
An Oracle DATEdoes not store times with more precision than a second. You cannot store millisecond precision data in a DATEcolumn.
OracleDATE不会以超过一秒的精度存储时间。您不能在DATE列中存储毫秒精度数据。
Your two options are to either truncate the string of the milliseconds before converting it into a DATE, i.e.
您的两个选项是在将其转换为 a 之前截断毫秒的字符串DATE，即
to_date( substr('23.12.2011 13:01:001', 1, 19), 'DD.MM.YYYY HH24:MI:SS' )
or to convert the string into a TIMESTAMPthat does support millisecond precision
或将字符串转换为TIMESTAMP支持毫秒精度的字符串
to_timestamp( '23.12.2011 13:01:001', 'DD.MM.YYYY HH24:MI:SSFF3' )
回答by Mark J. Bobak
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.
TO_DATE 支持转换为 DATE 数据类型，不支持毫秒。如果您想要 Oracle 中的毫秒支持，您应该查看 TIMESTAMP 数据类型和 TO_TIMESTAMP 函数。
Hope that helps.
希望有帮助。
回答by dipti
You can try this format SS.FFfor milliseconds:
您可以尝试这种格式SS.FF的毫秒数：
yyyy-mm-dd HH:MI:SS.FF
For more details:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions193.htm
更多详情：https:
//docs.oracle.com/cd/B19306_01/server.102/b14200/functions193.htm
回答by SANKAR
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'),
回答by Ishani Gupta
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());
