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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s