Oracle 错误：ORA-01839：指定月份的日期无效
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25871259/
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 error: ORA-01839: date not valid for month specified
提问by Ricardo Giaviti
OK guys, first of all, I have checked many websites about this error and, unfortunately, none of them helped me. I have the simple following query:
好的，首先，我已经检查了许多有关此错误的网站，但不幸的是，没有一个对我有帮助。我有以下简单的查询：
select * from (
select to_date(cal.year || cal.month || cal.day, 'yyyymmdd') as datew,
cal.daytype as type
from vw_calendar cal)
where datew > sysdate;
When I try to execute the entire query, this error shows up:
当我尝试执行整个查询时，出现此错误：
ORA-01839: date not valid for month specified
ORA-01839: 日期对于指定的月份无效
If I execute only the query:
如果我只执行查询：
select to_date(cal.year || cal.month || cal.day, 'yyyymmdd') as datew,
cal.daytype as type
from vw_calendar cal;
It worked absolutely fine. If you want to view the results of the query: http://pastebin.com/PV95g3ac
它工作得很好。如果要查看查询结果：http: //pastebin.com/PV95g3ac
I checked the days of the month like day 31 or leap year and everything seems correct. I don't know what to do anymore. My database is a Oracle 10g and I tried to execute the query on SQL Developer and PL/SQL Developer. Same error on both IDE.
我检查了一个月中的第 31 天或闰年，一切似乎都正确。我不知道该怎么办了。我的数据库是 Oracle 10g，我尝试在 SQL Developer 和 PL/SQL Developer 上执行查询。两个 IDE 上的相同错误。
回答by geek123
Actually the issue is some months have 30 days and some have 31, so the query which you are forming, it is trying to get 31st day of that month which is invalid and hence the error. For Example:
实际上，问题是有些月份有 30 天，有些月份有 31 天，因此您正在形成的查询试图获取该月的第 31 天，这是无效的，因此出现错误。例如：
it may be trying for date like: 31-NOV-2016 which is not correct, NOV has only 30 days.
它可能正在尝试这样的日期：31-NOV-2016 这是不正确的，NOV 只有 30 天。
回答by Ricardo Giaviti
Well, I found a workaround, not a solution. If anyone knows the "correct" solution for this problem, I appreciate if you share with us. My "solution" convert the date to number and then compare with a sysdate(converted too). Take a look:
好吧，我找到了解决方法，而不是解决方案。如果有人知道这个问题的“正确”解决方案，我很感激你与我们分享。我的“解决方案”将日期转换为数字，然后与 a sysdate（也已转换）进行比较。看一看：
select * from
( select to_number(cal.year||cal.month||cal.day) as datew,
cal.daytype as type from vw_calendar cal ) a
where a.datew > to_number(to_char(sysdate, 'yyyymmdd'));
Thanks to everyone!
谢谢大家！
回答by Vignesh Kumaresan
Use alias name for the inner query
为内部查询使用别名
select * from (
select to_date(cal.year || cal.month || cal.day, 'yyyymmdd') as datew,
cal.daytype as type
from vw_calendar cal) a
where a.datew > sysdate;
