Verify that the DateTime in the database is more than 90 days using the stored procedure

Question

UPDATE

Obviously, I did not include enough data, sorry!

What I need to do is set "campaign_Status" = 6 when "campaign_Date" is more than 90 days.

---

Hi,

I have a column (campaign_Date) in which DATETIME is stored. Using the stored procedure I need to check if the date is stored for 90 days (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 result in selection 1 if the campaign is out of date, 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 valid: it allows you to use an index to filter 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 DateAdd Function

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

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

Answer 3

Here's a version of the previous answers wrapped in a stored procedure (as it seemed, given):

 CREATE PROC sp_Campaign_Archive AS UPDATE [Campaign Table] SET Campaign_Status = 6 WHERE DateDiff(day,Campaign_Date,GetDate()) >= 90 GO 

Answer 4

 SELECT IIF(DATEDIFF(d, campaign_date, getdate()) >= 90, true, false) AS IsNinetyOrMoreDaysOld FROM myTable 

EDIT: If you want to select entries that are 90 days or more,

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

Answer 5

 select campaign_Date, case when getdate() - campaign_Date >= 90 then 'yes' else 'no' end as Is90DaysOldOrMore from MyTable 

UPDATE:

You can update records as follows:

 update MyTable set campaign_Status = 6 where getdate() - campaign_Date >= 90 

Since this status will quickly become obsolete because it depends on the date, you can make it a calculated column.