Purging data based on a datetime column

Question

Post reply

Purging data based on a datetime column

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DCPeterson SSCoach Points: 15266 More actions · Answer 1

DELETE

TableName

WHERE

ColumnName <= dateadd(d, -1, getdate())

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

If there is a time component unequal midnight in your data, you might want to use something like this

dateadd(d, -1, datediff(,d,0,getdate()))

Why? Notice the difference of both methods below:

select

dateadd(d, -1, getdate())

, dateadd(d, -1, datediff(d,0,getdate()))

------------------------------------------------------ ------------------------------------------------------

2005-04-07 09:42:25.080 2005-04-07 00:00:00.000

(1 row(s) affected)

The first case wouldn't bother about rows earlier than 09:42... on 20050407 while the second would catch all row, regardless of the time on 20050407

Just my $0.02 cents anyway.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Bill Dillon Valued Member Points: 70 More actions · Answer 3

Thanks a lot guys!!!

Don't know what I'd do without you all

Thanks again

Bill