Question:
I need a SQL statement to identify all rows that are more than
two years old and set my status flag based on my DATE column.
Is there a minus_years function in Oracle SQL?
If not, how can I make a SQL function to subtract two years in my
WHERE clause?
Can I use the add_years built-in function (BIF)?
Answer: Because Oracle is so flexible, there are several ways to
satisfy your request using
date
functions.
Also note here on how to do
Oracle arithmetic functions and you can also use date functions
when
scheduling jobs.
This primitive example demonstrates the concept, but it will not
always work because leap years will have 366 days:
update
mytab
set
status='OLD"
where
trunc(sysdate) < myrow_date -(365*2)
A correct solution would use the add_years BIF, something
like this:
update
mytab
set
status='OLD"
where
trunc(sysdate) < (myrow_date - add_years(myrow_date,2));
