Run SQL Job monthly at day 16. If it's a weekend, run it in the next business day ( dont care if is a holiday)

All we need is an easy explanation of the problem, so here it is.

I need to run a job every day 16. But if it is "saturday" or "sunday" it must run in the next business day.

I don’t care if it is holidays or not. I just need to run it.

I could do something like this and separete it on steps, and only pass to the next step if it runs with no error:

--Step 1 - Verify if it is 16
IF  day(getdate()) <> 16
    RAISERROR ('Job runs only day 16.',16,1) 

--Step 2 - verify if its saturday of sunday
IF  DATEPART(weekday,getdate()) IN ( 7,1 )
    RAISERROR ('Job will run on the next business day.',16,1)

I would create 2 steps with these queries, but this is not going to work since, lets say the next business day is 18, the first step will raise an error.

Is there a way to schedule something like this?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It’s not beautiful, but you could wrap all of your conditions into a CASE like so:

DECLARE @IsGood bit;
SET @IsGood = SELECT CASE WHEN DAY(GETDATE()) = 16 AND DATEPART(WEEKDAY, GETDATE()) NOT IN (1, 7) THEN 1
                          WHEN DAY(GETDATE()) IN (17, 18) AND DATEPART(WEEKDAY, GETDATE()) = 2 THEN 1
                          ELSE 0 END;

IF @IsGood <> 1
    RAISERROR ('Nope. Not today.', 16, 1);

Throw that into a scheduled job that runs on the 16th, 17th, and 18th of every month, and you’ll be good.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply