Generate Week Dates for a Month in SQL Server

In this post, we will see how to generate Week Start and End dates for a given Month in SQL Server.

Oftentimes one need to generate Start & End dates for all the weeks in a given month to generate week-wise report starting from Monday to Sunday to generate a week wise report

Below is the Table-Valued function where you have to pass year and month and it would return a table with all the weeks and its Start & End Dates for the given month.

CREATE FUNCTION dbo.udf_GetWeekDatesForMonth(
      @Year  SMALLINT
    , @Month TINYINT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN   
WITH Dates AS
(
    SELECT  DATEADD(mm,(@Year-1900)*12+@Month-1,0) AS MonthStartDate
          , DATEADD(mm,(@Year-1900)*12+@Month,0)-1 AS MonthEndDate
)
, Weeks AS
(
    SELECT  1 as weekno, MonthStartDate as StartDate
          , CASE WHEN DATEPART(WEEKDAY, MonthStartDate)= 1 THEN MonthStartDate ELSE DATEADD(d, (8 - datepart(WEEKDAY, MonthStartDate)), MonthStartDate) END AS EndDate
    FROM    Dates
    UNION ALL
    SELECT  weekno + 1, DATEADD(d, 1, EndDate)
          , CASE WHEN DATEADD(d, 7, EndDate)<=(select MonthEndDate from Dates) THEN DATEADD(d, 7, EndDate) ELSE (select MonthEndDate from Dates) END
    FROM    Weeks
    where   EndDate < (select MonthEndDate from Dates)
)
SELECT  'Week' + cast(weekno as varchar) as Week, StartDate, EndDate
FROM    Weeks

Let's execute a script to check the output of the function.

SELECT * FROM dbo.udf_GetWeekDatesForMonth(2015,10)

OUTPUT

Refer related post: Generating Week Dates for a year in SQL Server