SQL Jobs Schedule to run last working day in a month
I need to schedule a job the last working day of every month, Italian holyday calendar. First i need this function to calculate if a particular day is a workind day or not (Eastern day included). With this function the task is simple. Create a new Job schedule with Sql Server Agent, the job contains two step, the first check if the real job need to execute, the second step is the real job. The second step is executed only if the first succeeded. The check is simple:
DECLARE @data AS DATETIME
,@month AS INT
,@newdate AS DATETIME
,@newmonth AS INT
SELECT @data = getdate()
IF [dbo].[ufn_IsWorkingDay](@data) = 0
'Not working day'
SELECT @month = datepart(m, @data)
SELECT @newdate = [dbo].[ufn_NextWorkingDay](@data)
SELECT @newmonth = datepart(m, @newdate)
IF @newmonth = @month
'Not at the end'
If current date is not working day the we raise a simple error, so the second step do not will be executed. If the current day is a working day, calculate the next working day, if the mont part of the date is different then the current day is the last working day of the month so the second step can be executed.
The function ufn_NextWorkingDay is simple:
CREATE FUNCTION [dbo].[ufn_NextWorkingDay]
@Date datetime =GETDATE
declare @newdate datetime,@return as datetime
SELECT @newdate = dateadd(day, 1, @Date)
select @return = @newdate
select @return = [dbo].[ufn_NextWorkingDay](@newdate)