Selecting records where date range is current year minus #years

Question

I want to select and eventually delete records from a point in time earlier than current year minus "x" years.

I'm unsure of the most efficient way to do this.

Answer 1

This will delete anything three years older than January 1st of this year.

DELETE table_name 
WHERE change_date < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -36);

Answer 2

You can use extract() to get the actual year and compare those:

DELETE FROM table_name 
WHERE extract(year from change_date) <= extract(year from current_date) - 5;

If today is 2015 this will delete anything that is in 2010 or older.

Replace the - 5 with the "X" you want to use.

Note that extract(year from change_date) will not make use of an index on change_date itself. If performance is critical for this, you might want to create an index on the expression.

Answer 3

You'll want to review the DATEADD() function. So, a simple version is like this:

SELECT * 
FROM YourData 
WHERE DateField < DATEADD(yy, -1, GETDATE());

or

SELECT * 
FROM YourData 
WHERE DateField BETWEEN DATEADD(yy,-5, GETDATE()) and DATEADD(yy, -1, GETDATE());

So, this would give you data between 2010 and 2014. (yes you can add negative years)