oracle 尝试查找日期之间的天数时出现 ORA-30076
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18828074/
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
ORA-30076 when trying to find days between dates
提问by prabu
I was trying to find the no of days between 2 dates. I tried running the below query and ended up with ORA-30076error.
我试图找到两个日期之间的天数。我尝试运行以下查询并最终出现ORA-30076错误。
Query is,
查询是，
select extract(day from (sysdate - to_date('2009-10-01', 'yyyy-mm-dd')))
from dual
Error is,
错误是，
Error at Command Line:1 Column:34
Error report:
SQL Error: ORA-30076: invalid extract field for extract source
30076. 00000 - "invalid extract field for extract source"
*Cause: The extract source does not contain the specified extract field.
But when i try to run
但是当我尝试跑步时
Select extract(day from sysdate) from dual;
i got correct output.
我得到了正确的输出。
Please help if anyone knows the reason as why i am getting the above error.
如果有人知道我收到上述错误的原因，请帮忙。
回答by DoctorMick
Subtracting one date from another doesn't return a date, it returns a number that represents the number of days difference so you can't use extract.
从另一个日期中减去一个日期不会返回日期，它会返回一个表示天数差异的数字，因此您不能使用提取。
回答by Nick Krasnov
sysdate - to_date('2009-10-01', 'yyyy-mm-dd')will give the difference in daysbetween two dates - there is no need to use extractfunction. Moreover in this situation it would be incorrect usage of extractfunction simply because it would expect expression of date or interval datatype after fromclause and result of sysdate - to_date('2009-10-01', 'yyyy-mm-dd')subtraction is of number datatype - cause of the error you are facing.
sysdate - to_date('2009-10-01', 'yyyy-mm-dd')将给出两个日期之间的天数差异 - 无需使用extract函数。此外，在这种情况下，extract函数的使用将是不正确的，因为它期望在from子句之后表达日期或间隔数据类型，而sysdate - to_date('2009-10-01', 'yyyy-mm-dd')减法的结果是数字数据类型 - 您面临的错误的原因。
回答by Dan Bracuk
This:
这个：
sysdate - to_date('2009-10-01', 'yyyy-mm-dd')
returns a number, not a date. You can only extract days from dates.
返回一个数字，而不是日期。您只能从日期中提取天数。
