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.















      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 (CASEWHEN DATEPART(WeekDay,@CurDate) IN (1,7) THEN 1 ELSE 0 END)

      ELSE 0 END))=BEGIN

            SET @Matches = @Matches+1

            SET @TempDate = @CurDate



      IF @Matches=@Counter AND @Counter<32 BEGIN

      SET @Date=@TempDate


      SET @CurDate=DATEADD(DAY,1,@CurDate)




RETURN ISNULL(@Date,@TempDate)



The values we have are similar to the sysschedules table but I will list them here;
For @Type. 1=Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday, 7=Saturday
For @Counter, 1=1st, 2=2nd, 3=3rd, 4=4th etc..., 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, where it could be added to example our maintenance plans. This will run on the last Sunday of every month.


IF CONVERT(DATE,GETDATE())=(SELECT DatabaseUtilities.dbo.FirstDay(GETDATE(),1,32)) BEGIN
--Run your code here...
I hope this can be of use to you, or it gives you an idea for other processes.
Ousia Logo