Delete records from a previous month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Delete records from a previous month

Author

Topic

sfjtraps
Yak Posting Veteran

65 Posts

Posted - 2009-08-07 : 09:51:27

I have a date field in each record in my database with format:
7/29/2009 5:24:46 AM

Can I query for dates at least a month old and delete them?

Please help.

Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-07 : 09:53:34

a month old as in 30 days or anything previous month ?

previous month

where datecol < dateadd(month, datediff(month, 0, getdate()), 0)

30 days

where datecol < dateadd(day, datediff(day, 0, getdate()), -30)

KH
[spoiler]Time is always against us[/spoiler]

sfjtraps
Yak Posting Veteran

65 Posts

Posted - 2009-08-07 : 10:16:32

I want to delete any record 30 days or older. The following statement looks like it should:

DELETE
WHERE datecol < dateadd(day, datediff(day, 0, getdate()), -30)

Thank you for your help

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2009-08-07 : 10:20:15

DELETE
WHERE datecol < (getdate() - 30)

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2009-08-07 : 10:24:24

If this is a really large table and most of the records have a date field older than 30 days, then you should populate a new table with records that are 30 days and less old, drop the existing table and rename the new table to the old table. This could save a lot of time

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources