Someone recently asked me how to setup a job to run on a certain day. In this case it’s the 3rd day of the month. There is one simple solution which can do that. Unfortunately; it means a job has to run every day, but that’s not a big deal really.
You just have to create Step 1 to check on today. If today is NOT the 3rd day of the month then STOP the current job. If it is the current day, then do nothing and the job will proceed to the next step.
use master; set nocount on declare @today datetime = (select dateadd(d, -0, datediff(d, 0, getdate()))) declare @3rd datetime = (select dateadd(day, 2, dateadd(month, datediff(month, 0, getdate()), 0))) if @today <> @3rd begin print 'today is NOT the 3rd day' exec msdb..sp_stop_job 'my job name' end else print 'today is the 3rd'
You might be thinking… “Doesn’t this throw an error, or alert on the job?” Nope :) Basically what happens is the Job does not ‘fail’. It’s simply ‘cancelled’. This is basically the same thing whenever you right-click a job and select ‘Stop Job’. So although the agent history will show a nasty red icon next to the run history; it’s not a ‘failure’. It still only means it was stopped (cancelled) as you can see below by the Job history.
If you’re interested; here’s the quick test I created.
I created a 2 step Job.
Step 1: Check to see if it’s the 3rd day. If not; Stops the Job. If so… do nothing and continue to the next step as usual.
Step 2: Backup the Model database.
Here’s the results of the Job after it’s cancelled from within the Step 1.
I ran the Job a second time where @today = @today just to confirm the success, and it worked no problem.
Now; if you’re looking for a business day; you can try this:
use master; set nocount on declare @year_start datetime declare @year_finish datetime declare @today datetime = (select dateadd(d, -0, datediff(d, 0, getdate()))) declare @daydiff int declare @holidays table (Holiday datetime) declare @3rd_biz_day table ( [Date] datetime , [MonthName] varchar (50) , [DateName] varchar (50) , [BusinessDay] int ) set @year_start ='01 jan 2016' set @year_finish ='31 dec 2016' set @daydiff = datediff (day,@year_start,@year_finish)+1 insert into @holidays -- US Holidays select '1/1/2016' union all -- New Year's Day select '1/18/2016' union all -- Martin Luther King, Jr. Day select '2/15/2016' union all -- George Washington’s Birthday select '3/30/2016' union all -- Memorial Day select '6/4/2016' union all -- Independence Day select '9/5/2016' union all -- Labor Day select '10/10/2016' union all -- Columbus Day select '11/11/2016' union all -- Veterans Day select '11/24/2016' union all -- Thanksgiving Day select '12/26/2016' -- Christmas Day insert into @3rd_biz_day select * from ( select 'Date' = dateadd(day,daynum,@year_start) , 'MonthName' = datename(month,(dateadd(day,daynum,@year_start))) , 'DateName' = datename(weekday,(dateadd(day,daynum,@year_start))) , 'BusinessDay' = row_number() over (partition by (datepart(month,(dateadd(day,daynum,@year_start)))) order by (dateadd(day,daynum,@year_start))) from (select top (@daydiff) row_number() over(order by (select 1))-1 as daynum from sys.syscolumns sc_a cross join sys.syscolumns sc_b) dates where datename(weekday,(dateadd(day,daynum,@year_start))) not in ('saturday','sunday') and dateadd(day,daynum,@year_start) not in (select holiday from @holidays) ) sc_a where BusinessDay = 3 if @today not in(select [date] from @3rd_biz_day) begin print 'today is NOT the 3rd business day' exec msdb..sp_stop_job 'my job name' end else print 'today is the 3rd business day - continue to next job step'
Categories: Agent Jobs, Alerts, AlwaysOn, Automations, Backup & Restore, Big Data, Cluster, Command Prompt, Configuration, Database Mail, Database Mirroring, ELI5, Error Solutions, Failover, Failover Cluster, Featured, Folder System, Forensics & Auditing, High Availability, How To, Installations, Long Scripts, Management Studio, Mirroring, Notification, Powershell, Quick Fix, Scripts, Security, Short Scripts, SQL Stuff, Top, TSQL, Work Arounds