当我输入dateadd或datediff代码时，始终会收到此错误"ORA-00904"DATEADD"".无效的标识符." [英] When I enter a dateadd or datediff code i get this error all the time "ORA-00904 "DATEADD" INVALID IDENTIFIER."

问题描述

我有一个大学项目，并且有一个病人表，其中包含入院和出院日期属性.我需要删除7年以上的记录，我使用了以下代码:

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 :

delete from patient
where dis_date >= datedadd(yy,-7,getdate());

我收到错误

"ORA-00904:"DATEADD"无效标识符"

"ORA-00904: "DATEADD" invalid identifier"

. DATEDIFF函数也是如此.有其他选择吗?

. It's the same with the DATEDIFF function. Any alternatives please?

推荐答案

在Oracle中执行此操作的典型方法是:

The typical way of doing this in Oracle would be:

DELETE FROM patient
 WHERE dis_date < TRUNC(ADD_MONTHS(SYSDATE, -7*12));

我建议使用ADD_MONTHS()而不是年份间隔的原因是ADD_MONTHS()是leap年安全的.

The reason I suggest using ADD_MONTHS() instead of year intervals is that ADD_MONTHS() is leap-year safe.

这篇关于当我输入dateadd或datediff代码时，始终会收到此错误"ORA-00904"DATEADD"".无效的标识符."的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！