sql delete statement where date is greater than 30 days
I wish to find a SQL statement that when executed deletes fields that are older then 30 days.
My table “events” has a field “date” that contains the date and the time it was inserted in the database .
I am not at home right now so I can’t test this. is this good ?
SELECT * from Results WHERE [Date] >= DATEADD(d, -30, getdate())
4 Solutions collect form web for “sql delete statement where date is greater than 30 days”
Use DATEADD in your WHERE clause:
... WHERE date < DATEADD(day, -30, GETDATE())
You can also use abbreviation
dd instead of
DATEADD is probably the most transparrent way of doing this, it is worth noting
getdate()-30 will also suffice.
Also, are you looking for 30 days from now, i.e. including hours, minutes, seconds, etc? Or 30 days from midnight today (e.g. 12/06/2010 00:00:00.000). In which case, you might consider:
SELECT * FROM Results WHERE convert(varchar(8), [Date], 112) >= convert(varchar(8), getdate(), 112)
You could also set between two dates:
Delete From tblAudit WHERE Date_dat < DATEADD(day, -360, GETDATE()) GO Delete From tblAudit WHERE Date_dat > DATEADD(day, -60, GETDATE()) GO
Instead of converting to varchar to get just the day (convert(varchar(8), [Date], 112)), I prefer keeping it a datetime field and making it only the date (without the time).
SELECT * FROM Results WHERE CONVERT(date, [Date]) >= CONVERT(date, GETDATE())