SQL TO_DATE 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9401904/
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
TO_DATE problems
提问by acedanger
I have the following in my SQL where clause. This is running against an Oracle database. The sc_dtfield is defined in the db as a date field.
我的 SQL where 子句中有以下内容。这是针对 Oracle 数据库运行的。该sc_dt字段在数据库中定义为日期字段。
sc_dt = TO_DATE('2011-11-03 00:00:00.0', 'YYYY-MM-DD')
produces the following error "date format picture ends before converting entire input string"
产生以下错误 "date format picture ends before converting entire input string"
When I try to account for the fractional seconds (.0in this case) with the following, I get the following error.
当我尝试使用以下内容计算小数秒（.0在本例中）时，出现以下错误。
sc_dt = TO_DATE('2011-11-03 00:00:00.0', 'YYYY-MM-DD HH24:MI:SS.FF')
produces the following error "date format not recognized"
产生以下错误 "date format not recognized"
I'm really just assuming that I need the .FFto account for the .0in the "from" string. I've also tried .FF1, .FF2, ..., .FF9with the same results (I'm grasping at straws at this point).
我真的只是假设我需要.FF解释.0“from”字符串中的 。我也试过.FF1, .FF2, ... ，.FF9结果相同（此时我正在抓住稻草）。
As far as I can see, the sc_dtfield always has the month/day/year portion populated (and not the hour/minute/second portion).
据我所知，该sc_dt字段始终填充月/日/年部分（而不是小时/分钟/秒部分）。
I'm debugging a java program which is executing the above SQL as a prepared statement with the 2011-11-03 00:00:00.0value.
我正在调试一个 java 程序，该程序将上述 SQL 作为带有2011-11-03 00:00:00.0值的准备好的语句执行。
How can I get around this?
我怎样才能解决这个问题？
采纳答案by northpole
You need to use the seconds past midnight option. Something like:
您需要使用午夜后秒选项。就像是：
select TO_DATE('2011-11-03 00:00:01.1', 'YYYY-MM-DD HH24:MI:SS.SSSSS') from dual
Or This:
或这个：
select TO_TIMESTAMP('2011-11-03 00:00:00.1', 'YYYY-MM-DD HH24:MI:SS.FF') from dual
回答by Justin Cave
An Oracle DATEcolumn like sc_dtwill always have a day and a time component down to the second. Depending on your query tool and how it is configured (generally the session's NLS_DATE_FORMAT), it is possible that the time component isn't being displayed by default. You can, however, see the time component by doing an explicit TO_CHAR
DATE像Oracle列一样sc_dt总是有一个日期和一个精确到秒的时间组件。根据您的查询工具及其配置方式（通常是会话的NLS_DATE_FORMAT），默认情况下可能不显示时间组件。但是，您可以通过执行显式操作来查看时间分量TO_CHAR
SELECT to_char( sc_dt, 'YYYY-MM-DD HH24:MI:SS' )
FROM table_name
Because a DATEonly stores the time to the second, however, you cannot use fractional seconds in your format mask. So you would need to do something like this to extract just the portion of the string up to the fractional seconds. If you're not guaranteed that the string will always be 19 characters before the decimal point, you could use INSTRas well to look for the decimal point and take everything before that.
DATE但是，因为 a只将时间存储到秒，所以不能在格式掩码中使用小数秒。所以你需要做这样的事情来提取字符串的部分直到小数秒。如果您不能保证字符串总是在小数点前 19 个字符，您也可以使用INSTR查找小数点并在小数点之前获取所有内容。
TO_DATE( substr('2011-11-03 00:00:00.0', 1, 19), 'YYYY-MM-DD HH24:MI:SS')
Since this is coming from a Java application, however, you're much better off using the correct data type. If you bind a Java date (java.sql.Date) using the setDatemethod on the prepared statement rather than binding a string, then you won't have to deal with the string format in your SQL statement.
但是，由于这是来自 Java 应用程序，因此最好使用正确的数据类型。如果您使用预setDate准备语句上的方法绑定 Java 日期 (java.sql.Date)而不是绑定字符串，那么您将不必处理 SQL 语句中的字符串格式。
回答by spikendu
I realize this thread is more than a year old but... Another option just to throw it in might be:
我意识到这个线程已经有一年多的历史了，但是……另一种选择可能是：
src_dt=select TO_DATE('2011-11-03 00:00:01.1234', 'YYYY-MM-DD HH24:MI:SS.?????') from dual;
Note: there is an extra '?' thrown in to illustrate that you can even stick in a few extra '?'s. There is no complaint from Oracle if the digits represented by the '?'s do NOT have any corresponding character in the source time string. This might be helpful if you aren't sure of the precision of seconds you are receiving.
注意：有一个额外的“？” 扔进去是为了说明你甚至可以多加几个'?'。如果由 '?' 表示的数字在源时间字符串中没有任何相应的字符，则 Oracle 不会抱怨。如果您不确定接收到的秒数的精度，这可能会有所帮助。
This option gives some flexibility to the format of "fractional seconds" from your source time. I do not know that this is actually documented anywhere.
此选项为源时间的“小数秒”格式提供了一些灵活性。我不知道这实际上在任何地方都有记录。
回答by Dominique Kande Vita
I did this :
我这样做了：
ALTER SESSION
SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS.?';
--Change the decimal
ALTER SESSION
SET NLS_NUMERIC_CHARACTERS = ',.';
And it worked for me
它对我有用
回答by Teja
src_dt=select TO_DATE('2011-11-03 00:00:01.1', 'YYYY-MM-DD HH24:MI:SS.SSSSS') from dual
src_dt=select TO_DATE('2011-11-03 00:00:01.1', 'YYYY-MM-DD HH24:MI:SS.SSSSS') from dual
I guess the above one should work if you just need a date output.
我想如果你只需要一个日期输出，上面的应该可以工作。
