Post reply

How To Get week range of week number

  • November 21, 2013 at 6:42 am

    #279268

    Hi All,

    I need help with returning a week range.

    I need to pass in week number and year from a stored proc and return the output below but I don't want to limit the years and week numbers to the dates in my table, is there a way I can achieve this without using the dates in my table as I've done below.

    Exec spcTest @years = '2013,2012', @week = '47' --use split function to get the years

    Select CONVERT(VARCHAR, Convert(Varchar, (DATEADD(dd, -((DATEPART(dw, dteDate)+5)%7)-1, dteDate)), 111)) + ' - '+

    CONVERT(VARCHAR, Convert(Varchar, (DATEADD(dd, -((DATEPART(dw, dteDate)+5)%7)+5, dteDate)), 111)) as WeekRange,

    YEAR(dteDate) as [Year]

    From test

    WeekRangeYear

    2012/11/18 - 2012/11/242012

    2013/11/17 - 2013/11/232013

    Please help..

    Teee

  • November 21, 2013 at 7:27 am

    #1668119

    I'm sure someone here has a cleaner solution but this should work:

    DECLARE @WeekNum INT = 47

    DECLARE @FirstYear INT = 2012

    Declare @SecondYear int = 2013

    SELECT convert(varchar(10),DATEADD(WEEK, @WeekNum - 1,DATEADD(dd, 1 - DATEPART(dw, '1/1/' + CONVERT(VARCHAR(4),@FirstYear)), '1/1/' + CONVERT(VARCHAR(4),@FirstYear))),111) + ' - ' +

    convert(varchar(10),DATEADD(WEEK, @WeekNum,DATEADD(dd, 1 - DATEPART(dw, '1/1/' + CONVERT(VARCHAR(4),@FirstYear))-1, '1/1/' + CONVERT(VARCHAR(4),@FirstYear))),111)

    SELECT convert(varchar(10),DATEADD(WEEK, @WeekNum - 1,DATEADD(dd, 1 - DATEPART(dw, '1/1/' + CONVERT(VARCHAR(4),@SecondYear)), '1/1/' + CONVERT(VARCHAR(4),@SecondYear))),111) + ' - ' +

    convert(varchar(10),DATEADD(WEEK, @WeekNum,DATEADD(dd, 1 - DATEPART(dw, '1/1/' + CONVERT(VARCHAR(4),@SecondYear))-1, '1/1/' + CONVERT(VARCHAR(4),@SecondYear))),111)


    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • November 21, 2013 at 7:37 am

    #1668125

    Thank you 🙂 I'll try your solution..

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply