Auto delete datas from database

Question

Post reply

Auto delete datas from database

kanoe82

Valued Member

Points: 70
More actions
February 26, 2013 at 1:52 pm

#271349

Hello everyone,
You helped me pretty well last time so I hope you will be able to do the same this time 🙂
So I created a database on SQL server that I fill up with SSIS. I created a job with sql agent that update the database weekly. However, I also need to delete the old datas. So in my table (incident) I have a column named " created " . The format is " yyyy-mm-dd- "
The datas should be kept for 2 years. So, i am pretty sure that with a job I have the possibility to delete the old datas but I don't know how ...
Is it possible that every week, the job checks the datas and if they are two years old it deletes them ? If yes, how please?
Thank you 🙂

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

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

Abu Dina SSChampion Points: 14155 More actions · Answer 1

Sure, just add another step in your existing SQL agent job and add some tsql along the lines of:

DELETE FROM incident WHERE DATEDIFF(YEAR,CAST(getdate AS DATE), created) >2

Maybe it's better if you move the old data to an archive table in case you need it again? Just a suggestion

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

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

Lowell SSC Guru Points: 323507 More actions · Answer 2

but two years would be 730 days (365 x 2?), not 2, right?

DELETE FROM incident WHERE DATEDIFF(DD, CAST(getdate AS DATE), created) > 730

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Abu Dina SSChampion Points: 14155 More actions · Answer 3

Yea and this is why one shouldn't attempt to answer question on the iPhone at 9pm after work 😛

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

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

kanoe82 Valued Member Points: 70 More actions · Answer 4

haha well.. i forgive you ! 😛

Thank you very much, both of you , that is very helpful ! 🙂

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Abu Dina (2/26/2013)
Sure, just add another step in your existing SQL agent job and add some tsql along the lines of:
DELETE FROM incident WHERE DATEDIFF(YEAR,CAST(getdate AS DATE), created) > 2
Maybe it's better if you move the old data to an archive table in case you need it again? Just a suggestion

Lowell (2/26/2013)
but two years would be 730 days (365 x 2?), not 2, right?
DELETE FROM incident WHERE DATEDIFF(DD, CAST(getdate AS DATE), created) > 730

Actually, neither will work as the operands are wrong. You want the older date first, then the newer date:

DATEDIFF(DAY, CreatedDate, GETDATE())

Only problem with this is it won't make use of an index on the CreatedDate column. You really should use something more like this:

DELETE FROM incident

WHERE CreatedDate < DATEADD(YEAR, -2, CAST(GETDATE() AS DATE));