oracle 当我输入 dateadd 或 datediff 代码时，我总是收到此错误“ORA-00904 "DATEADD" INVALID IDENTIFIER。
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29145825/
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
When I enter a dateadd or datediff code i get this error all the time "ORA-00904 "DATEADD" INVALID IDENTIFIER."
提问by Frank
I have a university project and I have a patient table with admission and discharge date attributes. I need to delete records that are older than 7 years, I used the following code :
我有一个大学项目，我有一个包含入院和出院日期属性的患者表。我需要删除超过 7 年的记录，我使用了以下代码：
delete from patient
where dis_date >= datedadd(yy,-7,getdate());
I get the error
我收到错误
"ORA-00904: "DATEADD" invalid identifier"
“ORA-00904：“DATEADD”无效标识符”
. It's the same with the DATEDIFF function. Any alternatives please?
. 与 DATEDIFF 函数相同。请问有什么替代品吗？
回答by Rahul Tripathi
You may try this:
你可以试试这个：
DELETE FROM patient
WHERE dis_date < SYSDATE - INTERVAL '7' YEAR;
There is no function named as DATEADDin Oracle.
DATEADDOracle 中没有名为 as 的函数。
回答by David Faber
The typical way of doing this in Oracle would be:
在 Oracle 中执行此操作的典型方法是：
DELETE FROM patient
WHERE dis_date < TRUNC(ADD_MONTHS(SYSDATE, -7*12));
The reason I suggest using ADD_MONTHS()instead of year intervals is that ADD_MONTHS()is leap-year safe.
我建议使用ADD_MONTHS()而不是年份间隔的原因ADD_MONTHS()是闰年安全。
