Delete all data which are more than one week old

Hi there,
How to delete all the data which are more than week old in the SQL Server?
ThanxYou must execute the following SQL for every relevant table:

DELETE FROM TABLE WHERE datediff(day, DateStampColumn, getdate()) > 7

This requires that you know all the tables you want to delete data from and they all have a date stamp column.

I suspect this isn't as simple as you wanted but there really is no simpler completely generic way to do this.|||assuming DateStampColumn has an index, when you do this --

... where datediff(day, DateStampColumn, getdate()) > 7

you might force a table scan

(i have not tested this lately, i don't know how smart the latest optimizer is)

do this instead --

... where DateStampColumn < dateadd(day,-7,getdate())

this way, the expression on the right is evaluated first, then the resulting value can be compared to the index values

remember, internally datetime values are full integers|||Thanx fren.
I think i manage to do it already with the code u gave.
Thanx again|||Michael,

Be aware that though these examples use a hypothetical "DateStampColumn", they are NOT refering to a column of datatype "TimeStamp" in your table. A TimeStamp column is sequentially incremented, and is not in any way related to the actual time or date the data was changed. You need to apply these solutions to a DateTime column, and usually the method of setting the DateTime value is trickier than the process of identifying expired records.|||Thanx for your advice.
I'll sure will look into it.
Thanx again|||Hey, Are you'll spying over there. I have a similar requirement for once a month purging one months data. This is what I've come up with. SPecs: where working from a historical setup of 6 months:

select * FROM dbo.Temp_table where DATE_TIME < dateadd(day,-161,getdate())



Date from today back 5 months

2003-12-01 00:00:01.000

PS. all other field have been deleted except datetime and where tested before actual delete was run.