Delete items older than a day - SQL Server

Question

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

Answer 1

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

Or something like this... ;)

Answer 2

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

Raj

Answer 3

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

Answer 4

or

ts >= now() - INTERVAL 1 DAY

where ts is a name of the datetime column

Answer 5

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)