select and delete records older than 3 years

03-11-2021 12:33 PM
Occasional Contributor

We are attempting to maintain a dataset to include records only for the past 3 years.  New records are appended daily (via other scripted geoprocessing).

The table is in Enterprise SQL, and contains both varchar date (dd/mm/yyyy) and datetime (yyyy-mm-dd hh:mm:ss:ms) fields.

How do I select records that are 3 years older than yesterday (for deletion)?  I thought I was going down the correct path with this in SQL but I'm not getting far:

   DateTimeField < DATEADD(yy, -3, CONVERT(datetime, CONVERT(varchar, GETDATE(), 101)))

I need selections to the day, not the datetime (e.g. On March 11, the new records from March 10 are appended, and anything more than 3 years older than March 10 are deleted.  It's to the full date, not the actual time of day when the script is run.)  This selection will be used within a scheduled .py daily.



Tags (2)
0 Kudos
2 Replies
Occasional Contributor III

We use something like this where the days can be adjusted based on need for an extraction.

days = 20
"last_edited_date >= (CURRENT_Date-" + str(days) + ")


Occasional Contributor

Thanks Kara.  We're actually redesigning this project now.  I'm not sure if your suggestion would account for leap year days, etc.  Regardless, we're moving on for now but thanks again.

0 Kudos