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

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 2

See the DateAdd function

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

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