/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Thursday, December 10, 2015

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)

No comments:

Post a Comment

Recent Posts

My Blog List