Mark:

Thank you for offering to test this for me on a 2008 SQL Server.

Below is the script that I used.

I am running Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64) Jul 9 2014 16:04:25 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

CREATE FUNCTION dbo.GetWeeksForMonth

/**********************************************************************************************************************

Purpose:

Given any legal date for the DATETIME datatype, return the dates for the start and end of the week for all weeks in

the month represented by the given date while observing the date boundaries of the month.

http://www.sqlservercentral.com/Forums/Topic1612741-1292-1.aspx

Programmer's Notes:

1. The first date of the month is always returned as the first date of WeekStart.

2. The last date of the month is always returned as the last date of WeekEnd.

3. Intermediate rows return the start and end dates for a whole week that starts on Sunday.

4. ANY legal date/time allowed in the DATETIME datatype may be used.

Example Usage:

--===== Generic Syntax (@dt is DATETIME)

declare @dt datetime = '2014-08-25'

SELECT WeekNo, WeekStart, WeekEnd, DaysInWeek

FROM dbo.GetWeeksForMonth(@dt)

;

--===== Return weeks in current month

SELECT WeekNo, WeekStart, WeekEnd, DaysInWeek

FROM dbo.GetWeeksForMonth(GETDATE())

;

--===== Return weeks for August, 2015

SELECT WeekNo, WeekStart, WeekEnd, DaysInWeek

FROM dbo.GetWeeksForMonth('2014-08-15')

;

Revision History:

REV 00 - 12 Sep 2014 - Jeff Moden - Initial creation.

**********************************************************************************************************************/

--===== Define the I/O for this function

(

@dt datetime-- Pass any date value of month for which you need week info

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

--===== Return the weeks for the month of the given date.

WITH

cteMonth AS

(--==== Setup some constants to make other parts of the code easier.

SELECT FirstDay = DATEADD(mm,DATEDIFF(mm, 0,@dt), 0)

,LastDay = DATEADD(mm,DATEDIFF(mm,-1,@dt),-1)

,FirstSunday = DATEADD(dd,DATEDIFF(dd,-1,DATEADD(mm,DATEDIFF(mm,0,@dt),0))/7*7,-1)

),

cteWeeks AS

(--==== Create all of the week dates for a 6 week period. This is much faster than generating all dates for the whole month.

SELECT WeekNo = t.N

,WeekStart = CASE WHEN DATEADD(wk,t.N-1,m.FirstSunday) < m.FirstDay THEN m.FirstDay ELSE DATEADD(wk,t.N-1,m.FirstSunday) END

,WeekEnd = CASE WHEN DATEADD(wk,t.N,m.FirstSunday)-1 > m.LastDay THEN m.LastDay ELSE DATEADD(wk,t.N,m.FirstSunday)-1 END

FROM cteMonth m

CROSS APPLY (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) t (N)

)--==== This select only the correct weeks.

SELECT WeekNo, WeekStart, WeekEnd

,DaysInWeek = DATEDIFF(dd,WeekStart,WeekEnd)+1

FROM cteWeeks

WHERE WeekStart <= WeekEnd

;