forum

home / developersection / forums / function to generate week dates for a given year in sql server

Ask Question
Anonymous User

Other

I am a content writter !

Follow
Can you answer this question?
Answer

1 Answers

Anonymous User

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)

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

 

advertising
advertising

Liked By

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy