When I enter a dateadd or datediff code i get this error all the time "ORA-00904 "DATEADD" INVALID IDENTIFIER."

Question

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());

I get the error

"ORA-00904: "DATEADD" invalid identifier"

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

Solution 1

The typical way of doing this in Oracle would be:

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.

Solution 2

You may try this:

DELETE FROM patient
  WHERE dis_date  < SYSDATE - INTERVAL '7' YEAR;

There is no function named as DATEADD in Oracle.

Comments

[Mahesh]

Is this datedadd typo or error ?

[David Faber]

That will fail if the current date is February 29, 2016