Delete rows with date older than 30 days with SQL Server query
Sql ServerDate ArithmeticSql Server Problem Overview
I need a SQL statement to delete row that are older than 30 days.
My table events
has a field date
that contains the date and the time it was inserted in the database.
Will this work?
SELECT * from Results WHERE [Date] >= DATEADD(d, -30, getdate())
Sql Server Solutions
Solution 1 - Sql Server
Use DATEADD in your WHERE clause:
...
WHERE date < DATEADD(day, -30, GETDATE())
You can also use abbreviation d
or dd
instead of day
.
Solution 2 - Sql Server
You could also use
SELECT * from Results WHERE date < NOW() - INTERVAL 30 DAY;
Solution 3 - Sql Server
Although the DATEADD
is probably the most transparrent way of doing this, it is worth noting
that simply 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)
Solution 4 - Sql Server
To delete records from a table that have a datetime value in Date_column older than 30 days use this query:
USE Database_name;
DELETE FROM Table_name
WHERE Date_column < GETDATE() - 30
...or this:
USE Database_name;
DELETE FROM Table_name
WHERE Date_column < DATEADD(dd,-30,GETDATE())
To delete records from a table that have a datetime value in Date_column older than 12 hours:
USE Database_name;
DELETE FROM Table_name
WHERE Date_column < DATEADD(hh,-12,GETDATE())
To delete records from a table that have a datetime value in Date_column older than 15 minutes:
USE Database_name;
DELETE FROM Table_name
WHERE Date_column < DATEADD(mi,-15,GETDATE())
From: http://zarez.net/?p=542
Solution 5 - Sql Server
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
Solution 6 - Sql Server
We can use this:
DELETE FROM table_name WHERE date_column <
CAST(CONVERT(char(8), (DATEADD(day,-30,GETDATE())), 112) AS datetime)
But a better option is to use:
DELETE FROM table_name WHERE DATEDIFF(dd, date_column, GETDATE()) > 30
The former is not sargable (i.e. functions on the right side of the expression so it can’t use index) and takes 30 seconds, the latter is sargable and takes less than a second.
Solution 7 - Sql Server
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())
Solution 8 - Sql Server
GETDATE()
didn't work for me using mySQL 8
> ERROR 1305 (42000): FUNCTION mydatabase.GETDATE does not exist
but this does:
DELETE FROM table_name WHERE date_column < CURRENT_DATE - 30;
Solution 9 - Sql Server
Delete row older than 30 days.
SELECT * FROM TABLE_NAME where timestampString <= now() - interval 30 DAY;