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

Question

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 powershell is needed) would be appreciated. We are running SQL Server 2019 on Windows Server 2019.

To clarify, the job runs on the first of the month 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. So, maybe I can use something like: case datepart(dw,getdate()) = 1 then 'execute one job schedule' else 'execute another job schedule' end.

Answer 1

Since you actually only need to delay the job action by 75 minutes if the 1st of the month happens to be a Sunday, simply add the following as the first job step (with the regular 1st of the month schedule).

SET DATEFIRST 7; /* set the "first" day of the week to Sunday */
IF DATEPART(weekday, GETDATE()) = 1
BEGIN
    WAITFOR DELAY ''01:15:00'';
END;

i.e. set up the job like this:

USE [msdb];

DECLARE @job_id uniqueidentifier;
DECLARE @start_date_int int = CONVERT(int, CONVERT(varchar(8), GETDATE(), 112), 0);
DECLARE @schedule_id int;

EXEC dbo.sp_add_job @job_name = N'some job', @job_id = @job_id OUTPUT;

EXEC dbo.sp_add_jobstep 
      @job_id = @job_id
    , @step_id = 1
    , @step_name = N'wait 45 minutes if is a Sunday'
    , @command = N'SET DATEFIRST 7; /* set the "first" day of the week to Sunday */
IF DATEPART(weekday, GETDATE()) = 1
BEGIN
    WAITFOR DELAY ''01:15:00'';
END;
';

EXEC dbo.sp_add_jobstep
      @job_id = @job_id
    , @step_id = 2
    , @step_name = N'do the work'
    , @command = N'SELECT ''the work you want to do goes here'';';

EXEC dbo.sp_add_schedule
      @schedule_name = N'run on the 1st of the month'
    , @freq_type = 16 --monthly
    , @freq_interval = 1 --1st day of the month
    , @freq_recurrence_factor = 1
    , @active_start_date = @start_date_int
    , @active_start_time = 50000
    , @schedule_id = @schedule_id OUT;  -- 5:00:00 am

EXEC dbo.sp_attach_schedule 
      @job_id = @job_id
    , @schedule_id = @schedule_id;

Answer 2

Converted from a comment by this user

What I have done is to use datepart(dw,getdate()) and test for Sunday in the first job step. The systems I have worked on had Sunday as 1 so I used 1 / case datepart(dw,getdate()) = 1 then 1 else 0 end. If this step failed I would exit the job. The schedule would run on the first day of the month and if the first job step saw it was Sunday the rest of the steps would run.