Create a Job Schedule that runs on the 1st of month only if it falls on a Sunday

Question

Create a Job Schedule that runs on the 1st of month only if it falls on a Sunday

HookSQLDBA7

SSChampion

Points: 10709
More actions
January 1, 2023 at 12:03 am

#4132315

This job already has one schedule that runs on the first of the month. However, we need to run the job on a different schedule if the first of the month falls on a Sunday. It appears to me that the SQL Server Agent Job Properties Scheduler GUI is not flexible enough to handle this type of schedule. I am continuing to research this items. Any thoughts or examples (if powerhell is needed) would be appreciated. We are running SQL Server 2019 on Windows Server 2019.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101117 More actions · Answer 1

You could schedule a job to run on the first of the month, but immediately exit the job if that day is a Sunday. Add a second sched to that same job that runs on, say, the first Monday of the month, but immediately exit the job unless the previous day was Sunday / it is the second day of the month. And so on, to get the specific schedule you need.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

So the job must run on its original schedule and also if 1st of month is Sunday.

So you don’t want to change out the existing schedule or make changes to the job steps?

I would keep it relatively simple and have a 2nd agent job

“RunJob<name>FirstOfMonthWhenSunday” and do a schedule of the 1st of the Month at the needed time.

Then the contents would be in pseudo code

IF DATENAME(weekday,getdate()) = ‘Sunday’

BEGIN

EXEC msdb.dbo.sp_start_job (<jobname>)

END

ELSE

PRINT ‘1st wasn’t a Sunday’

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

Strongly suggest that you calculate the dates beforehand, insert the dates in a table and have the job check if the date exists in the table.

😎

From now until the 1st of May 2050, there are only 46 dates where the first of the month lands on a Sunday, there is no need to calculate this every day.

USE TEEST;
GO
SET NOCOUNT ON;
GO
DECLARE @START_DATE DATE = '20230101';
DECLARE @DURATION   INT  = 10000;
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
,  NUMS(N) AS (SELECT TOP(@DURATION) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N 
               FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,DATE_TABLE AS
(
    SELECT
        DATEADD(DAY,NM.N,@START_DATE) AS TDATE
    FROM NUMS NM
)
SELECT
    DT.TDATE
   ,DATENAME(WEEKDAY,DT.TDATE)
FROM DATE_TABLE DT
WHERE (DATEDIFF(DAY,0,DT.TDATE) % 7) = 6
AND   DATEPART(DAY,DT.TDATE) = 1;

TDATE      DAY_OF_WEEK
---------- ------------------------------
2023-10-01 Sunday
2024-09-01 Sunday
2024-12-01 Sunday
2025-06-01 Sunday
2026-02-01 Sunday
2026-03-01 Sunday
2026-11-01 Sunday
2027-08-01 Sunday
2028-10-01 Sunday
2029-04-01 Sunday
2029-07-01 Sunday
2030-09-01 Sunday
2030-12-01 Sunday
2031-06-01 Sunday
2032-02-01 Sunday
2032-08-01 Sunday
2033-05-01 Sunday
2034-01-01 Sunday
2034-10-01 Sunday
2035-04-01 Sunday
2035-07-01 Sunday
2036-06-01 Sunday
2037-02-01 Sunday
2037-03-01 Sunday
2037-11-01 Sunday
2038-08-01 Sunday
2039-05-01 Sunday
2040-01-01 Sunday
2040-04-01 Sunday
2040-07-01 Sunday
2041-09-01 Sunday
2041-12-01 Sunday
2042-06-01 Sunday
2043-02-01 Sunday
2043-03-01 Sunday
2043-11-01 Sunday
2044-05-01 Sunday
2045-01-01 Sunday
2045-10-01 Sunday
2046-04-01 Sunday
2046-07-01 Sunday
2047-09-01 Sunday
2047-12-01 Sunday
2048-03-01 Sunday
2048-11-01 Sunday
2049-08-01 Sunday
2050-05-01 Sunday

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 4

I don't believe you need a date table for this. It would just be another thing to worry about. I'd use a method similar to what Ant-Green used.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16164 More actions · Answer 5

you made it clear that you do not wish the job to run on the first of the month if it it is on a Sunday - but what do you do you EXACTLY wish to do if the first of the month is a Sunday? run it as part of another set of jobs? run it on following Monday?

you were not clear on that side of the requirements other than stating you wish it to run on different schedule.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 6

Jeff Moden wrote:

I don't believe you need a date table for this. It would just be another thing to worry about. I'd use a method similar to what Ant-Green used.

I do agree with you Jeff that given the sparse requirements posted, a date table might be an overkill. But what will happen if someone decides that the job cannot run on Friday the 13th?

😎

Other factors that can tilt the table, like execution frequency haven't been disclosed, is this a job that runs 100.000.000 times every day or does it only run on the first day of every month? A single data page table lookup might not be such a bad solution after all 😉

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 7

Eirikur Eiriksson wrote:

Jeff Moden wrote:
I don't believe you need a date table for this. It would just be another thing to worry about. I'd use a method similar to what Ant-Green used.
I do agree with you Jeff that given the sparse requirements posted, a date table might be an overkill. But what will happen if someone decides that the job cannot run on Friday the 13th? 😎 Other factors that can tilt the table, like execution frequency haven't been disclosed, is this a job that runs 100.000.000 times every day or does it only run on the first day of every month? A single data page table lookup might not be such a bad solution after all 😉

My suggestion is that a date table isn't needed for any of that. It's real easy to detect things like Friday the 13th in code and uses fewer resources.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed B Hall of Fame Points: 3669 More actions · Answer 8

Do you have two different schedules? What are those schedules like? Does it run multiple times per day or does it run once, but at a different time if it's Sunday? It's simple to discontinue the process if today is Sunday, but if you need the process to run at different times, you might need to create two schedules and another job that runs at midnight on the 1st to activate and deactivate the appropriate schedule. There's probably a cleaner way, but it depends on the nature of the different schedules.

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 9

Very simple example:

sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>

Just use a sqlcmd SQL script with the function below using a windows scheduled task in a batch file:

If datepart(dw,getdate()) = 7

...

DBASupport

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 10

cyrusbaratt wrote:

Very simple example:
sqlcmd -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>
Just use a sqlcmd SQL script with the function below using a windows scheduled task in a batch file:
If datepart(dw,getdate()) = 7
...

Just as a bit of a sidebar... I've been burned in the distant past by the "dw" datepart, especially with inter-country code. The same holds true with the "wk" date part. It only recognizes Sunday for DATEDIFF and DATEDIFF_BIG. Works fine for DATEADD but I've simply gotten out of the habit of using either.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 11

IF DATEPART(DAY, SYSDATETIME()) = 1 AND DATEDIFF(DAY, '19000101', SYSDATETIME()) % 7 = 6
BEGIN
    EXEC dbo.WhatEver;
END;

This reply was modified 1 year, 11 months ago by SwePeso.

N 56°04'39.16"
E 12°55'05.25"

HookSQLDBA7 SSChampion Points: 10709 More actions · Answer 12

In response to frederico_fonseca

This job already has one schedule that runs on the first of the month. However, we need to run the job on a different schedule if the first of the month falls on a Sunday. The job runs on the first of the starting at 5 A.M. If the first of the month falls on a Sunday, we need the job to start at 6:15 A.M. (We have 7 jobs that need this requirement. One of the jobs has 29 job steps. So, just duplicating the Jobs with a different job name and adding the second schedule may not be wise. If changes need to made they would have to be made in two places or jobs.)

HookSQLDBA7 SSChampion Points: 10709 More actions · Answer 13

This job already has one schedule that runs on the first of the month. However, we need to run the job on a different schedule if the first of the month falls on a Sunday. The job runs on the first of the starting at 5 A.M. If the first of the month falls on a Sunday, we need the job to start at 6:15 A.M. (We have 7 jobs that need this requirement. One of the jobs has 29 job steps. So, just duplicating the Jobs with a different job name and adding the second schedule may not be wise. If changes need to made they would have to be made in two places or jobs.)

Ed B Hall of Fame Points: 3669 More actions · Answer 14

I would create a second schedule that runs at 6:15 on the first and assign it to all seven jobs. The jobs will now have two active schedules.

Create another job that runs before 5am on the first and give that job a step that activates and deactivates the appropriate schedule based on whether it is a Sunday.

Make sure both schedules are clearly named and don’t assign them to any other jobs. Maybe add some kind of alert if either schedule is changed or a new job is assigned to one of them.