Function to Generate Week Dates for a Given Year In sql server

Anonymous User 1687 08 Dec 2015

How to Generate Week Dates for a Given Year In sql server please help me

Anonymous User

Other

I am a content writter !

1 Answers

Anonymous User 08 Dec 2015

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)

forum

Function to Generate Week Dates for a Given Year In sql server

Can you answer this question?

1 Answers