All Forums Database
samkv 7 posts Joined 08/14
04 Apr 2016
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.

jhsun3 7 posts Joined 09/13
04 Apr 2016

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

dnoeth 4628 posts Joined 11/04
04 Apr 2016
where Created_Date < add_months(trunc(current_date, 'y'), -12)

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

Dieter

samkv 7 posts Joined 08/14
05 Apr 2016

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

yuvaevergreen 93 posts Joined 07/09
06 Apr 2016

Shouldn't it be -24 for the calc?

dnoeth 4628 posts Joined 11/04
06 Apr 2016

Yep, of course.
This was just to force additional mental effort :-)

Dieter

You must sign in to leave a comment.