Last post Sep 30, 2015 12:10 PM by Bulldog248
Sep 30, 2015 12:23 AM|Bulldog248|LINK
I'm using Visual Web Developer 2010 Express with SQL Server 2008 Express, VB.
I need help writing a DELETE statement. I have a table with user names and dates along with other fields. I would like to automatically delete any entry over 30 days old.
The table has columns [Recipient] and [Date]. The recipient must match User.Identity.Name (no problem there) but how do I calculate the difference between DateTime.Now and the date in the [Date] column and wrap it up all in one SQL statement?
Sep 30, 2015 12:56 AM|nilsan|LINK
You could use
DATEDIFF ( datepart , startdate , enddate ) function.
use this function in select query and once you feel that all records are correct, use it in delete statement.
select * from yourtablename where DATEDIFF ( d, getdate(), [date] ) >= 30
I hope it helps
Sep 30, 2015 12:10 PM|Bulldog248|LINK
Thanks for your response, it was exactly what I needed except I had to reverse getdate() and [date]. The final line that worked is:
querystring = "DELETE FROM [Messages] WHERE [Recipient] = N'" & User.Identity.Name & "' and DATEDIFF ( d, [Date], getdate() ) >= 30"