• Or this, if you want to get rid of all Microsoft dependencies.

    This will work for SQL 2000 and onwards, and on all dates between 00010101 and 99991231.

    CREATE FUNCTION dbo.fnISOWEEK

    (

    @Year SMALLINT,

    @Month TINYINT,

    @Day TINYINT

    )

    RETURNS TINYINT

    AS

    BEGIN

    RETURN(

    SELECTCASE

    WHEN nextYearStart <= theDate THEN 0

    WHEN currYearStart <= theDate THEN (theDate - currYearStart) / 7

    ELSE (theDate - prevYearStart) / 7

    END + 1

    FROM(

    SELECT(currJan4 - 365 - prevLeapYear) / 7 * 7 AS prevYearStart,

    currJan4 / 7 * 7 AS currYearStart,

    (currJan4 + 365 + currLeapYear) / 7 * 7 AS nextYearStart,

    CASE @Month

    WHEN 1 THEN @Day

    WHEN 2 THEN 31 + @Day

    WHEN 3 THEN 59 + @Day + currLeapYear

    WHEN 4 THEN 90 + @Day + currLeapYear

    WHEN 5 THEN 120 + @Day + currLeapYear

    WHEN 6 THEN 151 + @Day + currLeapYear

    WHEN 7 THEN 181 + @Day + currLeapYear

    WHEN 8 THEN 212 + @Day + currLeapYear

    WHEN 9 THEN 243 + @Day + currLeapYear

    WHEN 10 THEN 273 + @Day + currLeapYear

    WHEN 11 THEN 304 + @Day + currLeapYear

    WHEN 12 THEN 334 + @Day + currLeapYear

    END + currJan4 - 4 AS theDate

    FROM(

    SELECTCASE

    WHEN (@Year - 1) % 400 = 0 THEN 1

    WHEN (@Year - 1) % 100 = 0 THEN 0

    WHEN (@Year - 1) % 4 = 0 THEN 1

    ELSE 0

    END AS prevLeapYear,

    CASE

    WHEN @Year % 400 = 0 THEN 1

    WHEN @Year % 100 = 0 THEN 0

    WHEN @Year % 4 = 0 THEN 1

    ELSE 0

    END AS currLeapYear,

    365 * (@Year - 1) + (@Year - 1) / 400 - (@Year - 1) / 100 + (@Year - 1) / 4 + 3 AS currJan4

    WHERE@Year BETWEEN 0 AND 9999

    AND @Month BETWEEN 1 AND 12

    AND @Day >= 1

    AND 1 =CASE

    WHEN @Month IN (1, 3, 5, 7, 8, 10, 12) AND @Day <= 31 THEN 1

    WHEN @Month IN (4, 6, 9, 11) AND @Day <= 30 THEN 1

    WHEN @Year % 400 = 0 AND @Day <= 29 THEN 1

    WHEN @Year % 100 = 0 AND @Day <= 28 THEN 1

    WHEN @Year % 4 = 0 AND @Day <= 29 THEN 1

    WHEN @Day <= 28 THEN 1

    ELSE 0

    END

    ) AS d

    ) AS d

    )

    END


    N 56°04'39.16"
    E 12°55'05.25"