October 1, 2002 at 10:42 am
I have a job that runs at 3:00 every day and executes a stored procedure to change pending and in-process flags. I want it to skip holidays, however. Is the best way to do this to make up a table of holidays and put some code at the beginning to see if GetDate is <> to any of those values? Or some other way?
October 1, 2002 at 10:48 am
Make a table. Holidays are such fickle things, change from company to company.
Or set a reminder in Outlook to disable the job.
Steve Jones
October 1, 2002 at 10:54 am
A table sounds like a good, option.
Another possibility, so the job only runs on the days you want would be to do something like this. Everyday the job runs, schedule the next run based on your criteria, most likely based on a table, and schedule the job with sp_add_jobschedule. Basically each schedule would be a onetime schedule, and not reoccuring. Also you will probably also need to use sp_delete_jobschedule as well to remove the schedule prior to adding a new one.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 1, 2002 at 11:43 am
thanks guys, how's this look. I'll just set a job to execute this code. The spoc is cnvflag.
BEGIN TRAN
DECLARE @holiday VARCHAR
SET @holiday = (SELECT * FROM Holidays WHERE HolidayDate = GetDate())
BEGIN
IF @holiday = NULL
EXEC cnvflag
END
COMMIT TRAN
October 1, 2002 at 12:03 pm
Be aware that when you specify the "=" sign when comparing dates fields they need to be the same down to the millisecond.
I'm guessing your holidays table contains a date that will not be an exact match with getdate() unless you happen to run it at the exact same millisecond, as the HolidayDate.
I think you would be better to have:
...
where convert(char(10),holiday_date,101)
select convert(char(10),getdate(),101)
or something that strips off the time.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 1, 2002 at 12:12 pm
Right, I'm just looking for the month, day, and year to match up. Using your format would the function GetDate() and the data-type datetime for Holiday_Date be comparable using the conversion you posted? Or perhaps just leave the dates in the holiday table as char's and convert the GetDate() value to char(10)? Again, thanks for the help. Just want to make sure I get it right.
October 1, 2002 at 12:22 pm
Sorry, forgot this.
BEGIN TRAN
DECLARE @holiday VARCHAR
DECLARE @CurrentDate VARCHAR
SET @CurrentDate = convert(char(10),getdate(),101)
SET @holiday = (SELECT * FROM Holidays WHERE convert(char(10),HolidayDate,101) = @CurrentDate)
BEGIN
IF @holiday = NULL
EXEC cnvflag
END
COMMIT TRAN
October 1, 2002 at 12:27 pm
I might make the holiday_date a char(10) and only do one conversion. Only because it make SQL work a little extra.
I would suggest you test yourself to make sure what ever code you implement works. Here is some code that you could use to test:
create table test_date (Holiday_date datetime)
insert into test_date values ('20021001')
insert into test_date values ('20021002')
insert into test_date values ('19021001')
select * from test_date
select * from test_date where
convert(char(10),holiday_date,101)
= convert(char(10),getdate(),101)
drop table test_date
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 1, 2002 at 12:31 pm
Sounds good, I'll test it all out. Thanks for all your help, especially the time comparison.
-Pat
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply