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.

SQL

ALTER FUNCTION FirstDay(@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<DATEADD(MONTH,1,@Month) AND @Date IS NULL BEGIN

     

      IF (SELECT (CASE @Type WHEN 1 THEN (CASE WHEN DATEPART(WeekDay,@CurDate)=THEN 1 ELSE 0 END)

      WHEN 2 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=THEN 1 ELSE 0 END)

      WHEN 3 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=THEN 1 ELSE 0 END)

      WHEN 4 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=THEN 1 ELSE 0 END)

      WHEN 5 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=THEN 1 ELSE 0 END)

      WHEN 6 THEN(CASE WHEN DATEPART(WeekDay,@CurDate)=THEN 1 ELSE 0 END)

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

      ELSE 0 END))=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

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.

SQL

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