Delete rows with date older than 30 days with SQL Server query

Question

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())

Actually.... Deletes "fields"?? You delete rows, not fields. Do you want to NULL the fields? — Colin Mackay, Dec 06 '10 at 09:33
Your SQL above will select rows where days is within the last 30 days. — Colin Mackay, Dec 06 '10 at 09:36
Does this answer your question? [how to get the 30 days before date from Todays Date](https://stackoverflow.com/questions/2833241/how-to-get-the-30-days-before-date-from-todays-date) — mickmackusa, Jul 14 '21 at 23:01
In reality, this is a typo question where you basically got the comparison symbols going in the wrong direction. — mickmackusa, Jul 14 '21 at 23:17
@ColinMackay nulling fields might actually be a legitimate thing to do when, for example, anonymizing data. — xorinzor, Jan 03 '23 at 08:13

score 155 · Accepted Answer · edited Oct 19 '16 at 14:45

155

Use DATEADD in your WHERE clause:

...
WHERE date < DATEADD(day, -30, GETDATE())

You can also use abbreviation d or dd instead of day.

edited Oct 19 '16 at 14:45

Tomas Kubes

23,880
18
111
148

answered Dec 06 '10 at 09:32

Colin Mackay

18,736
7
61
88

1

If you require 1 month instead of 30 days on average for month, than you can modify it to DATEADD(MONTH, -5, GETDATE()) – iMatoria Jan 24 '13 at 09:51
5

FUNCTION fu_dev.DATEADD does not exist. Why am i getting this error? fu_dev is my database. – Shaonline Oct 01 '15 at 10:00
@iMatoria Just to be clear it should be `DATEADD(MONTH, -1, GETDATE())`, using -5 would take off 5 months not 1. – Eborbob Dec 03 '19 at 15:49
@Colin; Amazing, I was able to quickly fix my query. But in a case where you deal with more than 3 dates in a query, can I still make use of this statement? – PatsonLeaner Feb 24 '20 at 09:05
@PatsonLeaner This is just one clause, you can filter on as many dates as you need by joining the clauses together with AND or OR. – Colin Mackay Feb 25 '20 at 11:29

Vikas Chauhan · Answer 2 · 2019-12-18T13:41:51.233

16

You could also use

SELECT * from Results WHERE date < NOW() - INTERVAL 30 DAY;

edited Dec 18 '19 at 13:41

answered Sep 21 '17 at 08:20

Vikas Chauhan

1,276
15
23

1

Good solution, but be careful using NOW() in your other queries, it won't cache it in further – muinh Dec 27 '19 at 09:25

James Wiseman · Answer 3 · 2010-12-06T09:49:59.060

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)

score 8 · Answer 4 · answered Feb 07 '18 at 17:06

8

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

answered Feb 07 '18 at 17:06

Ray Koren

814
14
25

The technique of using `DATEADD(GETDATE())` was a well-worn path on this page long before you posted. Your answer only contains redundant advice or if there is new advice it does not answer the posted question. In other words, the new parts are useless parts in respect to the posted question. Imagine if everyone did this when they wanted to post an answer -- they duplicate an earlier posted answer, then stretch the scope of the page to suit their own answer ...we would not have a good, clean, focused resource for researchers. – mickmackusa Jul 14 '21 at 22:56
1

Time of posting is irrelevant to good content curation on Stack Overflow. – mickmackusa Jul 14 '21 at 23:09

score 2 · Answer 5 · answered Dec 09 '19 at 03:23

2

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;

answered Dec 09 '19 at 03:23

markhorrocks

1,199
19
82
151

1

As per the tag on the question, the question relates to SQL Server, not mySQL. – Colin Mackay Feb 25 '20 at 11:31
It will also work for PostgresSQL. – markhorrocks Sep 25 '22 at 04:47

score 1 · Answer 6 · edited Sep 11 '12 at 06:42

1

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

edited Sep 11 '12 at 06:42

jonsca

10,218
26
54
62

answered Sep 11 '12 at 06:35

Chagbert

722
7
16

rjose · Answer 7 · 2018-09-19T12:15:24.807

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.

score 0 · Answer 8 · answered Feb 17 '17 at 14:41

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())

score 0 · Answer 9 · answered Feb 14 '22 at 11:23

0

Delete row older than 30 days.

SELECT * FROM TABLE_NAME where timestampString <= now() - interval 30 DAY;

answered Feb 14 '22 at 11:23

Prem prakash

41
4

Delete rows with date older than 30 days with SQL Server query

9 Answers9