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