Howard,
I'm on SQL Server 2012 so not sure if this helps or not. I've run Jeff's code through several month's and confirmed his logic is solid. Can you post your Function View and I'll try it on one of our 2008 installs?
Jeff, again thanks for your help! I've been digging into your code and am slowly understanding DATEADD. Working on CROSS APPLY now 🙂
Cheers!
Mark
SELECT WeekNo, WeekStart, WeekEnd, DaysInWeek
FROM dbo.GetWeeksForMonth(GETDATE())
Produces:
12014-09-01 00:00:00.0002014-09-06 00:00:00.0006
22014-09-07 00:00:00.0002014-09-13 00:00:00.0007
32014-09-14 00:00:00.0002014-09-20 00:00:00.0007
42014-09-21 00:00:00.0002014-09-27 00:00:00.0007
52014-09-28 00:00:00.0002014-09-30 00:00:00.0003
The Table-valued Function looks like:
USE [xxxxxxYour Data base Name]
GO
/****** Object: UserDefinedFunction [dbo].[GetWeeksForMonth] Script Date: 9/17/2014 7:47:45 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER 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.
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)
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
;