oracle to_date 函数不接受格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5240587/
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
oracle to_date function not accepting format
提问by Victor
When I do this in oracle 10g:
当我在 oracle 10g 中执行此操作时：
select to_date(trunc(SOMEINPUTdATE)) from table1
where to_date(trunc(date_column_timestamp),'MM/DD/YYYY')
>= to_date('03/11/2011' ,'MM/DD/YYYY')
I get: ORA-01843: not a valid monthif I change to : 'YYYY/MM/DD', it works.
But 'MM/DD/YYYY'is a valid format right?
我得到：ORA-01843: not a valid month如果我更改为 : 'YYYY/MM/DD'，它会起作用。但是'MM/DD/YYYY'有效的格式对吗？
回答by Jeffrey Kemp
You're getting things mixed up a bit. TO_DATE converts a string into a DATE. If date_column_timestampis already a date, you don't need to convert it to a date.
你把事情搞混了。TO_DATE 将字符串转换为 DATE。如果date_column_timestamp已经是日期，则无需将其转换为日期。
select trunc(SOMEINPUTdATE) from table1
where trunc(date_column_timestamp)
>= to_date('03/11/2011' ,'MM/DD/YYYY')
The ORA-01843is caused by the implicit conversion of a date to string. In other words, the following:
这ORA-01843是由日期到字符串的隐式转换引起的。换句话说，以下内容：
to_date(trunc(date_column_timestamp),'MM/DD/YYYY')
is equivalent to (assuming the default date format DD-MON-YYYY):
相当于（假设默认日期格式DD-MON-YYYY）：
to_date(TO_CHAR(trunc(date_column_timestamp),'DD-MON-YYYY'),'MM/DD/YYYY')
So, the TO_CHARreturns something like '11-MAR-2011', which then causes to_dateto fail because the date formats do not match.
因此，TO_CHAR返回类似'11-MAR-2011'，然后导致to_date失败，因为日期格式不匹配。
The same problem exists in your selectclause. You don't need to_datearound a truncof a date column.
你的select条款也存在同样的问题。您不需要to_date围绕trunc日期列。
