Posted by Erland Sommarskog on 11/11/05 13:34

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/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Tables in Parent-Child order
• Prev in forum: Re: Something Basic
• Thread view: Re: How to delete data older than X days, without considering time

[Reply to this message]