Generate Week Dates for a Year in SQL Server

In this post, we will see how to generate week start and end dates for a given year in SQL Server.

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

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

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

Lets' execute the script and check the output

SELECT * FROM dbo.udf_GetWeekDatesForYear(2015)

OUTPUT

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