Week Start and End by Month

• hmbtx

  SSCarpal Tunnel

  Points: 4664

  More actions

  September 18, 2014 at 7:51 am

  #1746406

  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

  ;