Daily SQL Task to delete contents from a table

Question

Hey I was wondering how I could set up a clean up task on a particular table to delete contents thats lets say a week old.

I am using SQL Server 2005

Answer 1

In SQL Server Management Studio, expand the SQL Server Agent, right click on "Jobs" and select "New Job..."

in "Steps", create a "New..." one and enter this:

DELETE YourTable WHERE YourDate<GETDATE()-7

or without regards to time use:

DELETE YourTable WHERE YourDate<DATEADD(day,DATEDIFF(day,0,GETDATE()-7),0)

in "Schedule", you can make it run every Sunday or whatever you need.

Answer 2

You can create a job that deletes everything older than a week. For example,

DELETE FROM MyTable
WHERE DateCreated <= dateadd(d, -7, getdate())

This assumes, though, that you have some way of tracking how old your records are in the table, and it also assumes there are no foreign key constraints.

You can then schedule the job to run when users aren't connected.