Technical Article

Return X Day of Month

,

Call the function just like a stored procedure to get your date.

Make this call from visual studio within a For Loop to get scheduling dates.

I use this function in conjunction with a .Net control that I created for scheduling recurring events. Users can set events on the Nth day of a given month or from a start date to an end date.

 

CREATE FUNCTION dbo.GetDayDate (@sd DATETIME,@ed DATETIME,@MonWhen INT,@DayofWeek NVARCHAR(20)) 
RETURNS NVARCHAR(10) 
AS 
/* Pass @sd = start date (mm/dd/yyyy), 
     @ed = end date (mm/dd/yyyy), 
     @MonWhen values (1,2,3,4,5)
        where   1 = First day of month
               2 = Second day of month
              3 = Third day of month
      4 = Fourth day of month
      5 = Last day of month
     @DayofWeek values (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)
*/ 
BEGIN 
DECLARE @datecounter DATETIME
SET @datecounter = @sd
DECLARE @MonthName NVARCHAR(20)
SET @MonthName = DATENAME(month,@sd)
DECLARE @wd AS INT
IF @DayofWeek='Monday'
    BEGIN
        SET @wd = 0
    END
IF @DayofWeek='Tuesday'
    BEGIN
        SET @wd = 1
    END
IF @DayofWeek='Wednesday'
    BEGIN
        SET @wd = 2
    END
IF @DayofWeek='Thursday'
    BEGIN
        SET @wd = 3
    END
IF @DayofWeek='Friday'
    BEGIN
        SET @wd = 4
    END
IF @DayofWeek='Saturday'
    BEGIN
        SET @wd = 5
    END
IF @DayofWeek='Sunday'
    BEGIN
        SET @wd = 6
    END
DECLARE @date AS DATETIME


WHILE @datecounter <= @ed
    BEGIN
        IF (SELECT DATENAME(MONTH, @datecounter))=@MonthName
            BEGIN
                IF @MonWhen = 1 
                BEGIN
                    SET @date = (SELECT CASE WHEN DATEPART(day,DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,6-DATEPART(day,@datecounter),@datecounter)), @wd)) > 7 Then DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,6-DATEPART(day,@datecounter),@datecounter)), @wd)-7
                            ELSE DATEADD(wk, DATEDIFF(wk,@wd, dateadd(dd,6-DATEPART(day,@datecounter),@datecounter)), @wd) END)
                END
                IF @MonWhen = 2 
                BEGIN
                    SET @date = (SELECT CASE WHEN DATEPART(day,DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,12-DATEPART(day,@datecounter),@datecounter)), @wd)) > 14 Then DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,12-DATEPART(day,@datecounter),@datecounter)), @wd)-7
                          ELSE DATEADD(wk, DATEDIFF(wk,@wd, dateadd(dd,12-DATEPART(day,@datecounter),@datecounter)), @wd) End)
                END
                IF @MonWhen = 3 
                BEGIN
                    SET @date = (SELECT CASE WHEN DATEPART(day,DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,18-DATEPART(day,@datecounter),@datecounter)), @wd)) > 21 Then DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,18-DATEPART(day,@datecounter),@datecounter)), @wd)-7
                            ELSE DATEADD(wk, DATEDIFF(wk,@wd, dateadd(dd,18-DATEPART(day,@datecounter),@datecounter)), @wd) End)
                END
                IF @MonWhen = 4 
                BEGIN
                    SET @date = (SELECT CASE WHEN DATEPART(day,DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,24-DATEPART(day,@datecounter),@datecounter)), @wd)) > 28 Then DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,24-DATEPART(day,@datecounter),@datecounter)), @wd)-7
                            ELSE DATEADD(wk, DATEDIFF(wk,@wd, dateadd(dd,24-DATEPART(day,@datecounter),@datecounter)), @wd) END)
                END
                IF @MonWhen = 5 
                BEGIN
                    SET @date = (SELECT CASE WHEN DATEPART(day,DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,30-DATEPART(day,@datecounter),@datecounter)), @wd)) > 28 then DATEADD(wk, DATEDIFF(wk,@wd, DATEADD(dd,30-DATEPART(day,@datecounter),@datecounter)), @wd)
                              ELSE DATEADD(wk, DATEDIFF(wk,@wd, dateadd(dd,24-DATEPART(day,@datecounter),@datecounter)), @wd) END)
                END
            END
         SET @datecounter = DATEADD(month,1,@datecounter)
     END 
RETURN CAST(CONVERT(NVARCHAR,@date,101) as nvarchar(10)) 
END

Rate

3 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (3)

You rated this post out of 5. Change rating