SQL for Deleting Data Older than n Years + current Year

Database
Enthusiast

SQL for Deleting Data Older than n Years + current Year

Hello, I want to delete data from a table where Created_Date older than first day of the year 2 years from today.  For example, today is 4/4/2016, I want to delete data older than 1/1/2014.

Can someone provide me the SQL for it?

Thanks.

5 REPLIES
Enthusiast

Re: SQL for Deleting Data Older than n Years + current Year

('01/01' || '/' ||TRIM((YEAR(CURRENT_DATE)-2) ))AS Dt_1

Junior Contributor

Re: SQL for Deleting Data Older than n Years + current Year

where Created_Date < add_months(trunc(current_date, 'y'), -12)

Get January 1st of the current year and subtract one year.

Enthusiast

Re: SQL for Deleting Data Older than n Years + current Year

Dieter,

Thanks.

where Created_Date < add_months(trunc(current_date, 'y'), -12)

This SQL below also worked, but I like your way. 

CAST

('01-01' || '-' ||TRIM(( EXTRACT (YEAR FROM CURRENT_DATE) - 2) )AS DATE FORMAT 'mm-dd-yyyy');

Enthusiast

Re: SQL for Deleting Data Older than n Years + current Year

Shouldn't it be -24 for the calc?

Junior Contributor

Re: SQL for Deleting Data Older than n Years + current Year

Yep, of course.

This was just to force additional mental effort :-)