Function to Return Specific Day of Month

SQL Server 2008, 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.

more: SQL Server flexible date Functions and calendar Table

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

SQL

What it does

Testing

Further reading