3

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.

1
  • You are looking for the mos efficient way? Which ways did you consider? How many rows/bytes do you want to delete/keep? What version of Oracle do you use?
    – miracle173
    Jul 31 '15 at 16:20
5

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);
2
  • Will this keep the current year as well? Ex: So on Aug 1st 2015, I want to keep all of 2015 (Jan 1 - Aug 1) and then all of 2014 and delete anything older.
    – user71835
    Jul 31 '15 at 14:32
  • If you use "DELETE table_name WHERE change_date < ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), -12);" you will delete anything older that January 1, 2014 at midnight. In other words anything from 2013 and before. -12 just means subtract 12 months from January 1st of this year.
    – Gandolf989
    Jul 31 '15 at 16:13
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.

-1

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)

4
  • You may want to ensure you understand how BETWEEN works. It's typically easier to understand later on if you use (DateField >= DATEADD(year,-5, GETDATE()) AND DateField <= DATEADD(year, -1, GETDATE()))
    – Hannah Vernon
    Jul 31 '15 at 14:42
  • Also, instead of using yy as the parameter for DATEADD() you should use the full name, year, since its much more concise and you won't inadvertently shoot yourself in the foot.
    – Hannah Vernon
    Jul 31 '15 at 14:44
  • You may want to add details about how to convert that into a DELETE statement, as well.
    – Hannah Vernon
    Jul 31 '15 at 14:46
  • 4
    The question is tagged oracle. There is no dateadd() function in Oracle. Jul 31 '15 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.