Delete entries after a month - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Delete entries after a month

Author

Topic

rtumatt
Starting Member

4 Posts

Posted - 2011-02-14 : 10:48:02

Hi guys iv been trying in vain to create a script which will be ran on a monthly basis which will delete entries in a database which are more than a month old. The date format is : 2010-12-16 09:52:37. Thanks for any reply!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-14 : 10:50:09

A month being 28 days, 30 days, 31 days? Or just everything prior to the current month?

Jim

Everyday I learn something that somebody else already knew

rtumatt
Starting Member

4 Posts

Posted - 2011-02-14 : 10:55:10

Hello jimf firstly thank you for your reply. I mean everything prior to the current month. Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-14 : 11:00:25

Use this
declare @date datetime
set @date= '2010-12-16 09:52:37'
set @date = dateadd(month,datediff(month,'18991201',@date),'18991201')

Jim

Everyday I learn something that somebody else already knew

rtumatt
Starting Member

4 Posts

Posted - 2011-02-14 : 11:05:27

Thanks for that ill take a look. Could you explain the code a little. What i had before was this see what your opinion is:

SELECT type FROM NetworkEvents
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= generatedAt;

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-14 : 11:09:31

Mine was done using MS SQL Server.

Jim

Everyday I learn something that somebody else already knew

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2011-02-14 : 11:15:25

I would do this differently.


DELETE FROM dbo.TableName
WHERE DateField BETWEEN DATEADD(MONTH,datediff(month,0,getdate())-1,0) AND DATEADD(day,-1,DATEadd(MONTH,datediff(month,0,GETDATE()),0))

Note this query ONLy does items from the prior month. Not everything prior. But you could adapt it easily using >.

The reason I suggest this being that if you operate on a table column in the WHERE clause you cannot use the indexes properly which can cause queries to run more slowly. All this does is compute the first and last days of the previousmonth and then do a between. This means it will not cause a full table scan and does not have to perform an opperation on every single row. Please see Chapter 2 of T-SQL Fundamentals by Itzik Ben-Gan for more details.

===
http://www.ElementalSQL.com/

rtumatt
Starting Member

4 Posts

Posted - 2011-02-14 : 11:21:43

Thanks for your reply Robert, I have just tried the SQL script and get 'Incorrect parameter count in the call to native function 'datediff'' Sorry im really new to SQL.

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2011-02-14 : 12:41:54

quote:
Originally posted by rtumatt

Thanks for your reply Robert, I have just tried the SQL script and get 'Incorrect parameter count in the call to native function 'datediff'' Sorry im really new to SQL.

Which version of SQL Server are you using? Also, post your actual code so we can see what the issue might be.

===
http://www.ElementalSQL.com/

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-14 : 13:17:19

This would be more secure, especially if there are timestamps included in the date field.

DELETE FROM dbo.TableName
WHERE DateField >= DATEADD(MONTH,datediff(month,0,getdate())-1,0)
and DateField < DATEADD(day,0,DATEadd(MONTH,datediff(month,1,GETDATE()),0))

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources