SQL Server Generate Week Dates for a Year and Month in SQL Server

SQL Server Generate Week Start Dates End Dates for a Year or Month in SQL Server

Sometimes, you might require Start & End dates for all the weeks in a given year or month to generate week-wise report starting from Monday to Sunday

I have developed a 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 a given year.

Getting Month Week Start Date and End Date

- - Totals by Week 

CREATE FUNCTION dbo.GetWeekDatesForMonth

        (@Year SMALLINT, @Month TINYINT)

RETURNS TABLE WITH SCHEMABINDING AS

 RETURN WITH 

 cteMonth AS   (SELECT  MonthStart     = DATEADD(mm,(@Year-1900)*12+@Month-1,0)

                       ,MonthEnd       = DATEADD(mm,(@Year-1900)*12+@Month,0)-1)

,cteWeeks AS   (SELECT  MonthStart

                       ,MonthEnd

                       ,FirstWeekStart = DATEADD(dd,DATEDIFF(dd,0,MonthStart)/7*7,0)

                       ,LastWeekStart  = DATEADD(dd,DATEDIFF(dd,0,MonthEnd  )/7*7,0)

                  FROM cteMonth)

 SELECT  [Week]       = 'Week'+LEFT(ca.Wk+1,1)

        ,StartDate    =  CAST(

                            CASE 

                            WHEN ca.Wk > 0 

                            THEN DATEADD(dd,Ca.Wk*7,FirstWeekStart) 

                            ELSE MonthStart

                            END

                        AS DATE)

        ,EndDate      = CAST(

                            CASE

                                WHEN DATEADD(dd,Ca.Wk*7+6,FirstWeekStart) <= MonthEnd 

                                THEN DATEADD(dd,Ca.Wk*7+6,FirstWeekStart) 

                                ELSE MonthEnd 

                            END 

                        AS DATE)

   FROM cteWeeks

  CROSS APPLY (SELECT TOP (DATEDIFF(dd,FirstWeekStart,LastWeekStart)/7+1)

                      t.N

                 FROM (VALUES (0),(1),(2),(3),(4),(5))t(N))ca(Wk);

Using GetWeekDatesForMonth 

-- Lets' execute the script and check the output 

SELECT * FROM dbo.GetWeekDatesForMonth(2015,12)

-- OR You can use the following function to get week start and end date

CREATE FUNCTION dbo.udf_GetWeekDatesForMonth(

    @Year  SMALLINT

    , @Month TINYINT

)

RETURNS @WeekDates TABLE (

    Week  VARCHAR(5)

    , StartDate DATE

    , EndDate DATE

)

AS

BEGIN

    DECLARE @MonthStartdate        DATE

            , @MonthEnddate        DATE

            , @WeekStartDate    DATE

            , @WeekEndDate        DATE

            , @ctr                INT = 0

    SET @MonthStartdate = CAST(CAST(@Year*100+@Month AS VARCHAR)+'01' AS DATE)

    SET @MonthEnddate = DATEADD(d,-1,DATEADD(m,1, @MonthStartdate))

    SET @WeekStartDate = @MonthStartdate

    SET @WeekEndDate = DATEADD(d, (8 - datepart(WEEKDAY, @WeekStartDate)), @WeekStartDate)

    WHILE @WeekStartDate < @MonthEnddate

    BEGIN

        SET @ctr = @ctr + 1

        SET @WeekEndDate = (CASE WHEN @WeekEndDate > @MonthEnddate THEN @MonthEnddate ELSE @WeekEndDate END)

        INSERT INTO @WeekDates

        SELECT 'Week' + cast(@ctr as varchar), @WeekStartDate, @WeekEndDate

        SET @WeekStartDate = DATEADD(d, 1, @WeekEndDate)

        SET @WeekEndDate = DATEADD(d, 6, @WeekStartDate)

    END

    RETURN

END

- -Lets' execute the script and check the output 

SELECT * FROM dbo.udf_GetWeekDatesForMonth(2015,12)

Generate Week Dates for a Year in SQL Server

CREATE FUNCTION dbo.udf_GetWeekDatesForYear(

    @Year  SMALLINT 

)

RETURNS @WeekDates TABLE (

    Week  VARCHAR(10)

    , StartDate DATE

    , EndDate DATE

)

AS

BEGIN

    DECLARE @YearStartdate  DATE

            , @YearEnddate  DATE

            , @WeekStartDate DATE

            , @WeekEndDate  DATE

            , @ctr    INT = 0

    SET @YearStartdate = CAST(CAST(@Year AS VARCHAR)+'0101' AS DATE)

    SET @YearEnddate = DATEADD(day,-1,DATEADD(year,1, @YearStartdate))

    SET @WeekStartDate = @YearStartdate

    SET @WeekEndDate = DATEADD(day, (8 - datepart(WEEKDAY, @WeekStartDate)), @WeekStartDate)

    WHILE @WeekStartDate < @YearEnddate

    BEGIN

        SET @ctr = @ctr + 1

        SET @WeekEndDate = (CASE WHEN @WeekEndDate > @YearEnddate THEN @YearEnddate ELSE @WeekEndDate END)

        INSERT INTO @WeekDates

        SELECT 'Week-' + cast(@ctr as varchar), @WeekStartDate, @WeekEndDate

        SET @WeekStartDate = DATEADD(d, 1, @WeekEndDate)

        SET @WeekEndDate = DATEADD(d, 6, @WeekStartDate)

    END

    RETURN

END

- -Lets' execute the script and check the output 

SELECT * FROM dbo.udf_GetWeekDatesForYear(2015)