Need to create an ongoing job that deletes all recs from a table and only retain last 21 days based on a date field?
-
Need to create an ongoing job that deletes all recs from a table and only retain last 21 days based on a date field?
I'm trying to create a job that will figure out what the current date is and deletes everything except the last 21 days. Thanks for your help.
-
Try something like:
DELETE FROM TableX x
WHERE x.DateFields < GetDate() - 21
You may have to do some formatting of your date field if it contains time.
Select GetDate() - 21 yields 2004-03-24 15:38:02.337
as of the time that i ran the script.
Good Luck,
Pete
-
Peter, try to use this statement:
DELETE FROM TableX x
WHERE x.DateFields < dateadd(dd,-21,convert(varchar(8), getdate(),112))
With this statement, you can solve Grasshooper's problem with time.
You can reschedule the job to run daily or weekly during non-busy time.
Regards,
kokyan 
-
You could use the datediff function like this :
DELETE FROM TableX x
WHERE DATEDIFF(dd,x.DateFields,getdate()) > 21
-
Use function either user-defined-function or system function may cause the query analyzer do not use indexes (if any) when doing queries for deletion. If not counting on performance, then Bert's method is neat and easier to understand.
Regards, kokyan -
I do this on a few tables every night to get rid of old and useless data. Here's how I do it:
delete from tableX
where datefield < convert(varchar, getdate() - 21, 101)
By putting the style on the convert, it will remove the time portion of the getdate() function, therefore it will keep the records starting 21 days ago at midnight.
Hope this helps.
Jarret
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply