Purging data based on a datetime column

Question

Post reply

Purging data based on a datetime column

Bill Dillon

Valued Member

Points: 70
More actions
April 7, 2005 at 4:20 pm

#92798

Hi ALL:
I'm new to SQL, so hope the question isn't too stupid.
I'd like to set up a job that would purge data from a table that is > 24 hours old. delete from table where datecolumn >= ???

the datecolumn is a datetime format. I need help on the ??? Poking around it looks like id declare fromdate datetime and use the getdate function and go from there. it's the "from there" I need help with.
Thanks in advance
Bill

Viewing 4 posts - 1 through 3 (of 3 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