How to delete data older than X days, without considering time

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.

Can you please post a SQL query that will not give me this headache?

thanx a lot allHi there,

You have to convert the source column to a non-using time format like
ISO:

DELETE FROM MNT_R
WHERE VARCHAR(10),MNT_R.TIMESTAMP < CONVERT(VARCHAR(10),GETDATE()-
30,112)

HTH, Jens Suessmeyer.|||nai (nioannides@.laiki.com) writes:
> 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.
> Can you please post a SQL query that will not give me this headache?

Instead of getdate() used convert(char(8), getdate(), 112) to strip
of the time portion.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx