blinkie23
asked on
delete records older than 30 days
I am trying to write a stored procedure, but mainly the sql, to delete all records older than 30 days. The extra part is that there are two tables, sharing a key, and I only want the records from the 2nd table deleted if the date value in the first table is older than 30 days from current date. The first table data should remain untouched.
This is a simple query to select data across the 2 tables. m.f_timestamp is the datetime type value I need to compare.
SELECT
m.f_message_id,
m.f_timestamp
a.f_attachment
FROM dbo.t_message AS m,
dbo.t_attachements AS a
WHERE
m.f_message_id=a.f_message
This is a simple query to select data across the 2 tables. m.f_timestamp is the datetime type value I need to compare.
SELECT
m.f_message_id,
m.f_timestamp
a.f_attachment
FROM dbo.t_message AS m,
dbo.t_attachements AS a
WHERE
m.f_message_id=a.f_message
if you want it to run all the time, you'd want to use something like:
where datediff(dd, f_timestamp, getdate()) > 30
where datediff(dd, f_timestamp, getdate()) > 30
Try this if you want 30 days fromt he current date
Delete
from t_attachments a
where a.f_message_id in (select f_message_id from dbo.t_message where f_timestamp < Convert(Datetime, Convert(Varchar, DateAdd (d, -30, getDate()), 112))
if current date is 01 Jul 2005, it will delete all records before 01 Jun 2005 i.e. from 31 May 2005
Delete
from t_attachments a
where a.f_message_id in (select f_message_id from dbo.t_message where f_timestamp < Convert(Datetime, Convert(Varchar, DateAdd (d, -30, getDate()), 112))
if current date is 01 Jul 2005, it will delete all records before 01 Jun 2005 i.e. from 31 May 2005
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great answers all. Acperkins, I came up with that easy-to-read solution on my own as well, and am delighted that your answer matches mine so exactly.
If I am not mistaken @acperkins answer does not take into acct the time factor such that if the getdate() returns 01 jul 2005 18:30 then the records from 01 Jun 2005 18:30 will get deleted thus including some records from 01 June. However my solution takes that into acct.
what do you say @acperkins?
what do you say @acperkins?
>>what do you say @acperkins?<<
It is true. But then you are making the assumption that "30 days from current date" should not include the time. I was making the assumption the questioner meant "30 days from current date" including the time or did not care, one way or the other, as it was merely intended as a method for clearing out old records, without considering down to the time level.
In conclusion, if that is the case and you want a fast simple delete use my solution. If on the other hand, you meant to delete all records older than 30 days from midnight tonight than consider randeeps solution (I have not tested it).
Thanks for the points.
It is true. But then you are making the assumption that "30 days from current date" should not include the time. I was making the assumption the questioner meant "30 days from current date" including the time or did not care, one way or the other, as it was merely intended as a method for clearing out old records, without considering down to the time level.
In conclusion, if that is the case and you want a fast simple delete use my solution. If on the other hand, you meant to delete all records older than 30 days from midnight tonight than consider randeeps solution (I have not tested it).
Thanks for the points.
Delete
from t_attachments a
where a.f_message_id in (select f_message_id from dbo.t_message where f_timestamp<='6/1/2005')