删除超过一天的项目 - SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1588722/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete items older than a day - SQL Server
提问by kjv
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) ?
在我的 datatase 的表中，我有一个 datatime 列，用于存储添加记录的时间。当我运行存储过程（考虑到当前时间）时，如何删除所有早于一天的记录？
回答by KB22
回答by gbn
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)
24 hours?
Yesterday midnight? Run code on 19th October, delete rows before 18th?
DATEDIFF：它使用白天边界午夜，因此在 10 月 19 日 00:05 运行它，您将删除 6 分钟前的行（10 月 18 日 23:59）
24小时？
昨天午夜？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 yesterdaymidnight:
这假设昨天午夜：
DELETE
MyTableWhere
WHERE
MyColumn < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -1)
回答by Raj
Delete <TableName>
Where DATEDIFF(day, <ColumnName>, getdate()) > 0
Raj
拉吉
回答by John Lechowicz
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 " 并使用更新语句将旧条目设置为 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 是您的记录创建日期或时间戳日期的位置
回答by KeyOfJ
Assuming date column to be "RecordCreatedDate"
假设日期列是“RecordCreatedDate”
DELETE FROM yourtable WHERE RecordCreatedDate < DATEADD(d, -1, GETDATE())
从你的表中删除记录创建日期 < 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.
我只是提醒，如果您的数据库有数百万行，您应该在 RecordCreatedDate 列上有一个索引，如果您要删除大量数据，可能会进行较小的批量删除。
回答by user1451111
delete from YourTable where DateColumn < getdate()-1
从 YourTable 中删除其中 DateColumn < getdate()-1
回答by Zepplock
or
或者
ts >= now() - INTERVAL 1 DAY
ts >= now() - 间隔 1 天
where ts is a name of the datetime column
其中 ts 是日期时间列的名称
