Check if DateTime in DB is more than 90 days old via Stored Procedure

Question

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.

---

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

Any help would be great.

Thanks.

Answer 1

See the DateAdd function

http://msdn.microsoft.com/en-us/library/ms186819.aspx

SELECT *
FROM MyTable
WHERE Campaign_Date <= DateAdd (d, -90, GetDate())

Answer 2

This will return all old campaigns:

SELECT  *
FROM    mytable
WHERE   campaign_Date <= DATEADD(day, -90, GETDATE())

This will select 1 if campaign is old, 0 otherwise:

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.

This will update all old campaigns with status 6:

UPDATE  mytable
SET     campaign_status = 6
WHERE   campaign_Date <= DATEADD(day, -90, GETDATE())

Answer 3

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,

SELECT campaign_date
FROM myTable
WHERE DATEDIFF(d, campaign_date, getdate()) >= 90

Answer 4

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.

Answer 5

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