topic select and delete records older than 3 years in Python Questions

select and delete records older than 3 years

JustinWolff — Thu, 11 Mar 2021 20:33:21 GMT

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.

Thanks

Justin

Re: select and delete records older than 3 years

Kara_Shindle — Thu, 11 Mar 2021 20:41:16 GMT

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) + ")

Re: select and delete records older than 3 years

JustinWolff — Thu, 25 Mar 2021 20:35:45 GMT

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.