Function to Return Specific Day of Month
This has been useful for our maintenance plan, as we can use one script and add code that will run on a specific day (like the last Sunday, or 1st Weekday), I have also used it for forecasting the times that Jobs will run on our servers.
The function only requires three inputs, the month you are interested in, the type, and the number.
It uses similar information as Microsoft use for monthly job schedules.
SQL
CREATE FUNCTION dbo.GetMonthDay(@Month DATETIME,@Type INT, @Counter INT) RETURNS DATETIME AS BEGIN
SET @Month = CONVERT(DATE,DATEADD(DAY,1-DATEPART(DAY,@Month),@Month))
DECLARE @CurDate DATETIME=@Month,@Date DATETIME,@Matches INT=0,@TempDate DATETIME
WHILE @CurDate
IF (SELECT (CASE @Type WHEN 1 THEN (CASE WHEN DATEPART(WeekDay,@CurDate)=1 THEN 1 ELSE 0 END)
WHEN 2 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=2 THEN 1 ELSE 0 END)
WHEN 3 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=3 THEN 1 ELSE 0 END)
WHEN 4 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=4 THEN 1 ELSE 0 END)
WHEN 5 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=5 THEN 1 ELSE 0 END)
WHEN 6 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=6 THEN 1 ELSE 0 END)
WHEN 7 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=7 THEN 1 ELSE 0 END)
WHEN 8 THEN(CASE WHEN DATEPART(WeekDay,@CurDate) IN (1,2,3,4,5,6,7) THEN 1 ELSE 0 END)
WHEN 9 THEN(CASE WHEN DATEPART(WeekDay,@CurDate) IN (2,3,4,5,6) THEN 1 ELSE 0 END)
WHEN 10 THEN (CASE WHEN DATEPART(WeekDay,@CurDate) IN (1,7) THEN 1 ELSE 0 END)
ELSE 0 END))=1 BEGIN
SET @Matches = @Matches+1
SET @TempDate = @CurDate
END
IF @Matches=@Counter AND @Counter<32 BEGIN
SET @Date=@TempDate
END
SET @CurDate=DATEADD(DAY,1,@CurDate)
END
RETURN ISNULL(@Date,@TempDate)
END
GO
What it does
The values we have are similar to the sysschedules table but I will list them here;
@Type
- 1=Sunday
- 2=Monday
- 3=Tuesday
- 4=Wednesday
- 5=Thursday
- 6=Friday
- 7=Saturday
- 8=any day up to the counter or last day of the month
- 9=any weekday up to the counter or the last day of the month
- 10=any Saturday or Sunday up to the counter or the last occurrence of the month
@Counter
- 1=1st
- 2=2nd
- 3=3rd
- 4=4th
- 32=Last (these are slightly different)
The steps it follows are as follows;
- Set the input date to the first day of the month
- Declare variables for our loop through the month
- Loop through each date and update out match count if there is a match, and update a temporary date to hold the last match
- If the matches equal our counter, and the counter is less than 5 (5=last day) then set our return date as the temp date, this will then exit the loop as the date is no longer null.
- Add one to our current date and perform our check again.
- Return our date, or if null the temp date which was our last match.
Below is an example of it in use. This will run every Sunday of February.
You can use it in a IF clause to determine whether or not to run certain parts of a stored procedure.
Testing
SELECT Utilities.dbo.GetMonthDay('2020-02-11',1,1) --2020-02-02 00:00:00.000
SELECT Utilities.dbo.GetMonthDay('2020-02-11',1,2) --2020-02-09 00:00:00.000
SELECT Utilities.dbo.GetMonthDay('2020-02-11',1,3) --2020-02-16 00:00:00.000
SELECT Utilities.dbo.GetMonthDay('2020-02-11',1,4) --2020-02-23 00:00:00.000
SELECT Utilities.dbo.GetMonthDay('2020-02-11',1,32)--2020-02-23 00:00:00.000
IF Utilities.dbo.GetMonthDay('2020-02-11',1,32)='2020-02-23' BEGIN
SELECT 'Run your code'
END
Further reading
I hope this can be of use to you, or it gives you an idea for other processes.
If you need to calculate with holidays then you will need a calendar table, and you can see a great example on the link below.
