# Calculate All Dates For Weeks that Overlap a Month

```

SET DATEFIRST 1;
DECLARE @paramDate DATE ='2015-08-04'

--- --get date
--Select dateadd(month,datediff(month,0,@paramDate),0) --Month start
--,dateadd(month,datediff(month,0,@paramDate)+1,0)----start of next month
--,datepart(week, dateadd(month,datediff(month,0,@paramDate),0)) ----start week number in a year
-- ,datepart(week, dateadd(month,datediff(month,0,@paramDate)+1,0))  ---- end week number in a year

--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,mydates as (
Select   dateadd(day,n-7, dateadd(month,datediff(month,0,@paramDate),0) ) dt
from Nums
--you can modify this where condition to have the day range you need.
WHERE n<  45  ) -- including days extended to previous month and next month

select datepart(week, dt) WeekNum,  dt DayDate,  datename(weekday,dt) DayStr from mydates

WHere
datepart(week, dateadd(month,datediff(month,0,@paramDate),0))<=datepart(week, dt)
AND datepart(week, dateadd(month,datediff(month,0,@paramDate)+1,0)) >=datepart(week, dt)

SET DATEFIRST 7;

```

http://forums.asp.net/p/2067756/5965730.aspx?p=True&t=635778199497626560

Advertisements