Sunday, 14 September 2014

Getting first day and last day of month

DECLARE @WeekDay AS INT = 7;
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;