• Better late than never, I guess...

    DECLARE @Year    CHAR(4)

    DECLARE @NextYear CHAR(4)

    DECLARE @ISOWeek VARCHAR(2)

        SET @Year    = '2001'

        SET @ISOWeek = '53'

        SET @NextYear = @Year+1

     

    SELECT

    CASE

    WHEN @ISOWeek > 0

     AND DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek-1)*7)

       < DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@NextYear),0)

    THEN DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek-1)*7)

    ELSE 0

    END AS StartDate,

           DATEADD(wk,DATEDIFF(wk,0,'01/04/'+@Year),0)+((@ISOWeek)*7)-1 AS EndDate

    --todo... needs some error checking for the week number...

    It returns a "0" or 01/01/1900 for the start date if the week was not found for the year...

    I'm thinking that there's no ISO week 53 in 2001 but I might be using the wrong ISO "standard".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)