删除超过一天的项目 - 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) ?

在我的数据库中的表中，我有一个datatime列，用于存储添加记录的时间。如何在运行存储过程时删除所有超过一天的记录（考虑当前时间）？

7 个解决方案

#1

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

您可以使用datediff和getdate函数构建DELETE语句。

Usage example:

用法示例：

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

#2

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

说到SQL，你必须用“比一天更早”来指定你的意思。

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)

DATEDIFF：它使用午夜边界，所以在10月19日00:05运行它你将删除6分钟的行（10月18日23:59）
24 hours?

24小时？
Yesterday midnight? Run code on 19th October, delete rows before 18th?

昨天午夜？ 10月19日运行代码，在18日之前删除行？

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

另外，不要在列上放置函数。

This assumes 24 hours to the minute:

这假设24小时到分钟：

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

This assumes yesterday midnight:

假设昨天午夜：

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

#3

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

Raj

拉吉

#4

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.

我通常建议不要实际删除数据库中的数据，因为你不知道什么时候你可能需要返回并恢复或回滚到以前的记录，因为数据损坏或审计等。相反，我会添加一些类标题，如“IsDeleted” “并使用更新语句将day old条目设置为true。

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

DateCreatedOn是您创建记录或时间戳日期的地方

#5

Assuming date column to be "RecordCreatedDate"

假设日期列为“RecordCreatedDate”

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

删除来自yourtable WHERE RecordCreatedDate （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.

我只是提醒说，如果您的数据库有数百万行，那么您应该在RecordCreatedDate列上有一个索引，如果要删除大量数据，可能会进行较小的批量删除。

#6

delete from YourTable where DateColumn < getdate()-1

从YourTable中删除DateColumn （）>

#7

-1

要么

ts >= now() - INTERVAL 1 DAY

ts> = now（） - INTERVAL 1天

where ts is a name of the datetime column

其中ts是datetime列的名称

#1