How to Compare Date every day automatically? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to Compare Date every day automatically?

Author

Topic

savvy
Starting Member

48 Posts

Posted - 2006-02-03 : 05:01:07

I got various Job Details listed in my table. I have set all jobs in a such a way that they will be LIVE when Flag=1 and EXPIRED when Flag=0. But how can I compare the Expiry Job Date with the Current Date and Update Flag=0 automatically when its expired. I probably need compare everyday.
I have no clue how to do this. I looked at Triggers but may be its not the one i'm looking for..
Is this possible in SQL Server?
Thanks in Advance for your time and help

Savvy

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-03 : 05:09:02

You Can create a job scheduled daily.
In Job Step
You can write an update query
-----------
Update table set flag = 0
when Expiryjobdate = getdate()

savvy
Starting Member

48 Posts

Posted - 2006-02-03 : 05:12:28

Thanks for your fast reply and help
Are you talking about Task Schedular in Windows or something else?

Savvy

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-03 : 05:14:54

I am talking about Sql Server Jobs.

savvy
Starting Member

48 Posts

Posted - 2006-02-03 : 05:17:57

sorry mate I got no idea about Sql Server Jobs Schedules
Can u give a small example or give a bit of more info on how to set it ?
I really appreciate your help

Savvy

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-03 : 05:23:29

quote:

Update table set flag = 0
when Expiryjobdate = getdate()

This might not work as getdate() will return the current date & time. If you ExpiryJobDate is without time, do this

where ExpiryJobDate > dateadd(day, 0, datediff(day, 0, getdate()))

>> You Can create a job scheduled daily.
shallu1_gupta means the scheduled job in Enterprise Manager - Management - SQL Server Agent - Jobs

Alternatively, you can do away of the daily job to update the Flag and just use add the where statement where you check for Flag = 1 or 0


-- Expired Job :
ExpiryJobDate >  dateadd(day, 0, datediff(day, 0, getdate()))
-- Live Job :
ExpiryJobDate <= dateadd(day, 0, datediff(day, 0, getdate()))

----------------------------------
'KH'

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-02-03 : 05:26:56

Yeah i mean the same
Enterprise Manager - Management - SQL Server Agent - Jobs

and replace getdate() by dateadd(day, 0, datediff(day, 0, getdate()))
rightly said by khtan
as it will ignore the time part.

savvy
Starting Member

48 Posts

Posted - 2006-02-03 : 05:37:59

Thank you very much mates
I'll give a try. I'll think i got the info which i need about the Jobs and the Alternative
Thanks once again for your help

Savvy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources