CODEWITHSUNDEEP
sqlsql-serverdatetime
kjv
kjv

Reputation: 11327

Delete items older than a day - SQL Server

In a table in my datatase I have a datatime column which stores the time at which the record is added. How can I delete all records which are older than a day when I run a stored procedure (considering the current time) ?

Upvotes: 21

Views: 63430

Answers (7)

user1451111
user1451111

Reputation: 1943

delete from YourTable where DateColumn < getdate()-1

Upvotes: 0

KeyOfJ
KeyOfJ

Reputation: 675

Assuming date column to be "RecordCreatedDate"

DELETE FROM yourtable WHERE RecordCreatedDate < DATEADD(d, -1, GETDATE())

I only caution that if your database has millions of rows your should have an index on the RecordCreatedDate column and possibly do smaller batch deletes if you will be removing large amounts of data.

Upvotes: 0

KB22
KB22

Reputation: 6979

You can build a DELETE statement making use of the datediff and the getdate functions.

Usage example:

DELETE FROM yourTable WHERE DATEDIFF(day,getdate(),thatColumn) < -1

Upvotes: 31

gbn
gbn

Reputation: 432431

When it comes to SQL, you have to specify what you mean by "older than a day".

  • DATEDIFF: it uses day boundary midnight so run it at 19th October 00:05 and you'll delete rows 6 minutes old (18th October 23:59)

  • 24 hours?

  • Yesterday midnight? Run code on 19th October, delete rows before 18th?

Also, don't put a function on a column.

This assumes 24 hours to the minute:

DELETE
    MyTableWhere
WHERE
    MyColumn < DATEADD(day, -1, GETDATE())

This assumes yesterday midnight:

DELETE
    MyTableWhere
WHERE
    MyColumn < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -1)

Upvotes: 30

Zepplock
Zepplock

Reputation: 29165

or

ts >= now() - INTERVAL 1 DAY

where ts is a name of the datetime column

Upvotes: -1

John Lechowicz
John Lechowicz

Reputation: 2583

I generally advise against actually deleting data from your database because you never know when you may need to go back and recover or rollback to previous records because of data corruption or an audit, etc. Instead I would add an bit column title something like "IsDeleted" and set day old entries to true using an update statement.

Something like

'UPDATE tablename SET IsDeleted = 1 WHERE (DATEDIFF(day,DateCreatedOn,GETDATE()) > 0)

Where DateCreatedOn is where your record's created on or timestamp date would go

Upvotes: 0

Raj
Raj

Reputation: 10853

Delete <TableName>
Where DATEDIFF(day, <ColumnName>, getdate()) > 0

Raj

Upvotes: 5

Related Questions