# Calculate All Dates For Weeks that Overlap a Month [Answered]RSS

## 2 replies

Last post Sep 14, 2015 09:32 AM by limno

Member

1 Points

12 Posts

### Calculate All Dates For Weeks that Overlap a Month

Sep 12, 2015 03:07 PM|RobSK|LINK

Greetings,

I am trying to create some SQL that will give me all weeks (Mon -> Sunday) that overlap a given month.  The month is specified by a parameter whose format is flexible... could be a date within the month, or could be the string or numeric version of month/year.  Either way I would have the numeric week (1 through 5) and the date in that week.

For example if the parameter was the date: "2015-08-13" (e.g. august 13 of this year), the result set would be:

1, July  27, 2015  (Monday)

1, July 28, 2015 (tuesday)

1, July 29, 2015 (Wed)

1, July 30, 2015 (Thurs)

1, July 31, 2015 (Fri)

1, August 1, 2015 (Sat)

1, August 2, 2015 (Sun)

2, August 3, 2015 (Mon) ... and so on

In this way it is entirely possible that the dates can be from a previous/next month.  I could probably calculate this pretty easily in C# code... but in T-SQL it's pushing my abilities.  Does anyone have any input on how to do this?

Thx,

Rob

Member

1 Points

12 Posts

### Re: Calculate All Dates For Weeks that Overlap a Month

Sep 12, 2015 06:01 PM|RobSK|LINK

Update:  So I managed to create a rough first pass at it.. but am sure it's less than elegant.  Any suggestions on how to refine or simplify are welcome.

IF OBJECT_ID('tempdb..#DateTable') IS NOT NULL
TRUNCATE TABLE #DateTable
ELSE
CREATE TABLE #DateTable
(
WeekNum INT,
DayDate DATE,
DayStr varchar(10)
)
GO

SET DATEFIRST 1;

DECLARE @paramDate DATE ='2015-08-04'
DECLARE @startDate DATE=CAST(MONTH(@paramDate) AS VARCHAR) + '/' + '01/' + + CAST(YEAR(@paramDate) AS VARCHAR) -- mm/dd/yyyy
DECLARE @endDate DATE= DateAdd(day, (8-DatePart(weekday,
DateAdd(Month, 1+DateDiff(Month, 0, @startDate), 0)))%7,
DateAdd(Month, 1+DateDiff(Month, 0, @startDate), 0))
DECLARE @currDate DATE =DATEADD(wk, DATEDIFF(wk,0,@startDate), 0)
--SET @endDate
DECLARE @weekCounter INT = 0

WHILE (@currDate < @endDate)
BEGIN
INSERT INTO #DateTable (WeekNum, DayDate, DayStr)
VALUES (1 + (@weekCounter /7), @currDate, datename(dw,@currDate))

SET @currDate = DATEADD(day, 1, @currDate)
SET @weekCounter = @weekCounter + 1
END
GO

SELECT * FROM #DateTable

• ### limno

All-Star

122095 Points

9654 Posts

Moderator

### Re: Calculate All Dates For Weeks that Overlap a Month

Sep 14, 2015 09:32 AM|limno|LINK

```  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 (
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