schedule jobs to skip holidays

  • 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?

  • 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

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • 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

  • 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

  • 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

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

  • 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

  • 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

  • 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