Monday, March 19, 2012

Compare timestamps then delete

What I am trying to do is create a stored procedure that compares a the current datetime to a datetime field already added to a table. I also want it to compare the two and if the old data that is collected in the table is over 6 months old I want it deleted.

Someone please help

Quote:

Originally Posted by JReneau35

What I am trying to do is create a stored procedure that compares a the current datetime to a datetime field already added to a table. I also want it to compare the two and if the old data that is collected in the table is over 6 months old I want it deleted.

Someone please help


delete ... where datediff(mm,datefield,getdate()) > 6|||

Quote:

Originally Posted by ck9663

delete ... where datediff(mm,datefield,getdate()) > 6


Will this function automatically change with each month. So I don't have to plug in the month everytime it changes.|||

Quote:

Originally Posted by JReneau35

Will this function automatically change with each month. So I don't have to plug in the month everytime it changes.


datediff() gets the difference between the start data and end date. the "mm" signifies you're trying to get the difference expressed in number of months. getdate() is a function that returns the system date.

essentially, you're deleting the record if the difference between the datefield (content of your field) and the system date is more then 6 months ... if you need to include 6 months and older, do a "=>" instead

No comments:

Post a Comment