June 12, 2008 at 12:34 am
Hello All,
is there anyway we can automatically delete the records older than a month from a table.
I have a table that has Date/time column , and anything older than a month needs to be deleted by running a job on a daily basis.
Thank you in Advance.
June 12, 2008 at 12:56 am
It is possible.
delete from TableName where ColumnName < getdate() - 31
June 13, 2008 at 12:56 am
Suresh B. (6/12/2008)
It is possible.delete from TableName where ColumnName < getdate() - 31
Good answer but I would rather go for:
delete from TableName where datediff(d,ColumName,getdate()) > 31. You might also want to use an average number of days since all months do not have 31 days and that is 30.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
June 13, 2008 at 2:53 am
Yes sure.
You can create a SQL job by using Enterprise Manager, schedule the job to run on daily basis and write following SQL stsement in Command text box of job creation wizard:
Delete From Tabel1
Where Column1 < Dateadd(M,-1,getdate())
Regards
Manish Mittal
June 13, 2008 at 7:22 am
Good one, I never thought of a maintenance plan. Be careful though to just delete data older than a month.
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
June 13, 2008 at 8:11 am
For finding the number of days in your select instead of a set value:
DELETE FROM Table WHERE Column < (GETDATE() - DAY(DATEADD (d, 1 - DAY(GETDATE()), GETDATE()) - 1))
/* Anything is possible but is it worth it? */
June 13, 2008 at 8:16 am
Manish Mittal (6/13/2008)
Yes sure.You can create a SQL job by using Enterprise Manager, schedule the job to run on daily basis and write following SQL stsement in Command text box of job creation wizard:
Delete From Tabel1
Where Column1 < Dateadd(M,-1,getdate())
Regards
Manish Mittal
Agreed - and writing it this way (instead of involving the column in the function) gives you a better change to retain index seeks rather than scans, so it would execute faster.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply