FUNCTION TO GENERATE WEEK DATES FOR A GIVEN YEAR IN SQL SERVER

Ankit Singh

Total Post:341

Points:2389
Posted by  Ankit Singh
 597  View(s)
Ratings:
Rate this:
How to Generate Week Dates for a Given Year In sql server please help me
  1. aditya kumar Patel

    Post:254

    Points:1810
    Re: Function to Generate Week Dates for a Given Year In sql server

    CREATE FUNCTION dbo.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 

    select *  from dbo.GetWeekDatesForYear(2015)

     

      Modified On Dec-08-2015 08:59:50 PM

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!