CODEWITHSUNDEEP
sqlsql-servert-sql
moe
moe

Reputation: 5249

Delete all data except for the last 2 days using SQL

I am trying to delete all data from my table except the last 2 days; also deleting today's data. 

delete from myTable where myDate > DATEADD(day, DATEDIFF(day, -2, GETDATE()), -2)

this is not working for me sample data

2015-03-06 18:00:00.000
2015-03-06 18:30:00.000
2015-03-06 19:00:00.000
2015-03-06 19:30:00.000
2015-03-06 20:00:00.000
2015-03-06 20:30:00.000
2015-03-06 21:00:00.000
2015-03-06 21:30:00.000
2015-03-06 22:00:00.000
2015-03-06 22:30:00.000
2015-03-06 23:00:00.000
2015-03-06 23:30:00.000
2015-03-05 00:00:00.000
2015-03-05 00:30:00.000

Upvotes: 0

Views: 3285

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270473

I would simply recommend:

delete
    from myTable
    where myDate > cast(getdate() - 2 as date);

This will work in SQL Server 2008+.

Upvotes: 1

Aidan
Aidan

Reputation: 171

This should do what you want. You were pretty close. What you want is the start of today minus 2 days, as below. Also, you were using the greater than operator (>) instead of the less than (<), which would have deleted everything in the last two days, but not before. 

delete from myTable 
where myDate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), -2)

Upvotes: 2

Related Questions