Nightly command to delete records>60 days old

[Guest_imported]

Good afternoon,

I have a MS SQL 7 DB with 1 table (the table is called ads) of data. I would like to delete records from the DB that over over 60 days in age. There is a column of data called AdDate with a datatype of DateTime. This contains the date (example - 2/8/2001 from <%=date%>) the record was submitted to the DB.

Is there a way to create a filter or some command that will delete * from ads where AdDate=today-60 or something along those lines, nightly at say 1am or thereabouts? Not to sure of how the querry would be constructed either but I figure I should check and see if there is a way to automate this.

Thanks!
Rob

 

[cdukes]

You could try something like the following:

delete from ads
where DateDiff (Day, adDate, GetDate ) > 90

This can then be run from SQLSever Agent as a job whenever you wish.

Hope this helps

Chris Dukes

 

[Phwrd]

simple but something like this might work...

delete from ads where (convert(varchar(25),AdDate,101))> DateAdd(day,-90,(convert(varchar(25),getdate(),101)))

good luck,

Pat