truncating data older than 30 days

Question

truncating data older than 30 days

Tsql-561486

SSC Eights!

Points: 821
More actions
December 14, 2007 at 7:55 am

#61476

Hi,
I have a table with
C1- ID
C2-Date
C3-Date with time
C4-quantity
I need to create a daily process that will remove the oldest data(data older than 30 days) on the table and make sure the table is with only 30 days worth of data.
Please help me.
Thanks in advance

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

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

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 1

The first thing to do is create a stored procedure to actually accomplish the deletion for you and test it safely. IT could be something as simple as :

create procedure DeleteOld

as

delete from TableName

where date < (getdate() - 30)

Then create the job to run it on a routine basis. In SQL Server 2005 this is as easy as using SSMS and going under SQL Server Agent, right clicking on jobs and choosing New Job... From there, the Wizard makes it quite painless. This of course assumes you have appropriate rights and SQL Server Agent is set up, but given those its relatively easy.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

GSquared SSC Guru Points: 260824 More actions · Answer 2

delete from table

where c2 < dateadd(day, -30, getdate())

That should do what you need. Set it up to run in Server Agent each day and you should be good to go.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 3

sqldba (12/14/2007)
Hi,
I have a table with
C1- ID
C2-Date
C3-Date with time
C4-quantity
I need to create a daily process that will remove the oldest data(data older than 30 days) on the table and make sure the table is with only 30 days worth of data.
Please help me.
Thanks in advance

See if this returns what you want

select columns from table

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

If it returns the required data, then convert that to DELETE

delete from table

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

Madhivanan

Failing to plan is Planning to fail

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

I don't know what this table is actually for, but it's generally a bad idea to just flush data down the toilet without carefully considering what that loss of data could cause a year from now.

Unless that data is available somewhere else (ie. files that have been downloaded, etc), I'd recommend that you copy the data into an archive table before you delete it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions