SQL 通过存储过程检查数据库中的日期时间是否超过 90 天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1487204/
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
Check if DateTime in DB is more than 90 days old via Stored Procedure
提问by Munklefish
UPDATE
更新
Evidently I didn't include enough data, sorry!
显然我没有包含足够的数据，对不起！
What I need to do is set 'campaign_Status' = 6 when 'campaign_Date' is more than 90 days old.
我需要做的是在 'campaign_Date' 超过 90 天时设置 'campaign_Status' = 6。
Hi,
你好，
I have a column (campaign_Date) which stores a DATETIME. Using a Stored Procedure I need to check if the stored date is 90 days old (or more).
我有一个列 (campaign_Date) 存储 DATETIME。使用存储过程我需要检查存储日期是否为 90 天（或更多）。
Any help would be great.
任何帮助都会很棒。
Thanks.
谢谢。
回答by Quassnoi
This will return all old campaigns:
这将返回所有旧广告系列：
SELECT *
FROM mytable
WHERE campaign_Date <= DATEADD(day, -90, GETDATE())
This will select 1if campaign is old, 0otherwise:
这将选择1广告系列是否是旧的，0否则：
SELECT CASE WHEN campaign_Date <= DATEADD(day, -90, GETDATE()) THEN 1 ELSE 0 END
FROM mytable
Note that the first query condition is sargable: it will allow using an index to filter the dates.
请注意，第一个查询条件是sargable：它允许使用索引来过滤日期。
This will update all old campaigns with status 6:
这将更新所有具有 status 的旧广告系列6：
UPDATE mytable
SET campaign_status = 6
WHERE campaign_Date <= DATEADD(day, -90, GETDATE())
回答by Raj More
See the DateAdd function
请参阅 DateAdd 函数
http://msdn.microsoft.com/en-us/library/ms186819.aspx
http://msdn.microsoft.com/en-us/library/ms186819.aspx
SELECT *
FROM MyTable
WHERE Campaign_Date <= DateAdd (d, -90, GetDate())
回答by Stu Pegg
Here's a variation on the previous answers, wrapped in a stored procedure (as seemed to be asked):
这是先前答案的变体，包含在存储过程中（似乎有人问过）：
CREATE PROC sp_Campaign_Archive AS
UPDATE [Campaign Table]
SET Campaign_Status = 6
WHERE DateDiff(day,Campaign_Date,GetDate()) >= 90
GO
回答by shahkalpesh
SELECT IIF(DATEDIFF(d, campaign_date, getdate()) >= 90, true, false)
AS IsNinetyOrMoreDaysOld
FROM myTable
EDIT: If you wish to pick records which are 90 or more days old,
编辑：如果您想选择 90 天或更长时间的记录，
SELECT campaign_date
FROM myTable
WHERE DATEDIFF(d, campaign_date, getdate()) >= 90
回答by RedFilter
select campaign_Date,
case when getdate() - campaign_Date >= 90 then 'yes' else 'no' end as Is90DaysOldOrMore
from MyTable
UPDATE:
更新：
You can update the records like this:
您可以像这样更新记录：
update MyTable
set campaign_Status = 6
where getdate() - campaign_Date >= 90
Because this status will go out of date rapidly because it is date-dependent, you could make it a calculated column instead.
由于此状态会因日期相关而迅速过时，因此您可以改为将其设为计算列。
