DECLARE @WeekDay AS INT = 7;
DECLARE @Date AS Date = getdate();
WITH DateLooper (CurrentDate, DayNum) AS (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0) AS CurrentDate,
DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)) AS DayNum
UNION ALL
SELECT DATEADD(DAY, 1, dl.CurrentDate) AS CurrentDate,
DATEPART(WEEKDAY, DATEADD(DAY, 1, dl.CurrentDate)) AS DayNum
FROM DateLooper dl
WHERE DATEPART(WEEKDAY, dl.CurrentDate) <> @WeekDay
)
SELECT dl.CurrentDate,
dl.DayNum
FROM DateLooper dl
ORDER BY dl.CurrentDate;
-------------------------------------------------------------
-- Select last occurence of desired day of week
-------------------------------------------------------------
WITH DateLooper (CurrentDate, DayNum) AS (
SELECT DATEADD( DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) + 1, 0)) AS CurrentDate,
DATEPART(WEEKDAY, DATEADD( DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) + 1, 0))) AS DayNum
UNION ALL
SELECT DATEADD(DAY, -1, dl.CurrentDate) AS CurrentDate,
DATEPART(WEEKDAY, DATEADD(DAY, -1, dl.CurrentDate)) AS DayNum
FROM DateLooper dl
WHERE DATEPART(WEEKDAY, dl.CurrentDate) <> @WeekDay
)
SELECT TOP 1
dl.CurrentDate,
dl.DayNum
FROM DateLooper dl
ORDER BY dl.CurrentDate ASC;
DECLARE @Date AS Date = getdate();
---------- First occurrence of a given day of week --------------------
WITH DateLooper (CurrentDate, DayNum) AS (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0) AS CurrentDate,
DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)) AS DayNum
UNION ALL
SELECT DATEADD(DAY, 1, dl.CurrentDate) AS CurrentDate,
DATEPART(WEEKDAY, DATEADD(DAY, 1, dl.CurrentDate)) AS DayNum
FROM DateLooper dl
WHERE DATEPART(WEEKDAY, dl.CurrentDate) <> @WeekDay
)
SELECT dl.CurrentDate,
dl.DayNum
FROM DateLooper dl
ORDER BY dl.CurrentDate;
-------------------------------------------------------------
-- Select last occurence of desired day of week
-------------------------------------------------------------
WITH DateLooper (CurrentDate, DayNum) AS (
SELECT DATEADD( DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) + 1, 0)) AS CurrentDate,
DATEPART(WEEKDAY, DATEADD( DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) + 1, 0))) AS DayNum
UNION ALL
SELECT DATEADD(DAY, -1, dl.CurrentDate) AS CurrentDate,
DATEPART(WEEKDAY, DATEADD(DAY, -1, dl.CurrentDate)) AS DayNum
FROM DateLooper dl
WHERE DATEPART(WEEKDAY, dl.CurrentDate) <> @WeekDay
)
SELECT TOP 1
dl.CurrentDate,
dl.DayNum
FROM DateLooper dl
ORDER BY dl.CurrentDate ASC;