Home > DeveloperSection > Forums > Function to Generate Week Dates for a Given Year In sql server
Ankit Singh

Total Post:341

Points:2389
Posted on    December-08-2015 8:55 PM

 Database SQL Server  SQL  SQL Server 2008  SQL Server 2012 
Ratings:


 1 Reply(s)
 332  View(s)
Rate this:
How to Generate Week Dates for a Given Year In sql server please help me


aditya kumar Patel

Total Post:250

Points:1782
Posted on    December-08-2015 8:59 PM

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

Don't want to miss updates? Please click the below button!

Follow MindStick