How to delete data older thna X days (not considering time)

[Nakis]

When running the following SQL statements, I get the same results.
Though I need to count only -30 days. Both statements below also consider the time of the day as well, which is not desired


DELETE FROM MNT_R
WHERE MNT_R.TIMESTAMP < GETDATE()- 30

DELETE FROM MNT_R
WHERE MNT_R.TIMESTAMP < DATEADD(d, -30, GETDATE())


Here is the format of the values in column
MNT_R.TIMESTAMP
2005-08-09 06:06:44.577
2005-08-09 06:06:46.810
2005-08-09 06:06:49.060

So, since data are inserted into the MNT_R table every few seconds, my delete statement will delete different number of rows, according to the time of the day it runs.

thanx a lot all

 

[SQLDenis]

Take a look at this FAQ that vongrunt wrote

http://www.tek-tips.com/faqs.cfm?fid=5842

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[Nakis]

Well I'm not a real expert, so I didn't find anything to help me with what I'm looking for.
If I run my SQL statement on Nov 11th 14:00pm, and want to delete all rows created 30 days ago, then I want it to capture all records inserted previous to that day (which is 30 days before Nov 11th), not just the ones inserted before 14:00 30 days ago.
I hope you can understand what I mean.

thanks again

 

[vongrunt]

OK then...

DELETE FROM MNT_R
WHERE DATEDIFF(d, MNT_R.TIMESTAMP, GETDATE()) > 30

This will remove all records with TIMESTAMP value older than 30 days - time fraction ignored (as explained between the lines in tip #2).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)