Here is a version which uses CONVERT to allow using the yyyy-mm-dd format. This makes the code independent from SQL Server installation flavors.

Ralf

-----

DECLARE @date datetime

SET @date = CONVERT(datetime, '2003-12-31', 120) -- 1

--SET @date = CONVERT(datetime, '2005-01-01', 120) -- 53

--SET @date = CONVERT(datetime, '2005-01-05', 120) -- 1

--SET @date = CONVERT(datetime, '2006-01-01', 120) -- 52

SELECT @date,

CASE

-- Exception where @date is part of week 52 (or 53) of the previous year

WHEN @date =

CASE (DATEPART(dw, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)) + @@DATEFIRST - 1) % 7

WHEN 1 THEN CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)

WHEN 2 THEN DATEADD(d, -1, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

WHEN 3 THEN DATEADD(d, -2, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

WHEN 4 THEN DATEADD(d, -3, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

WHEN 5 THEN DATEADD(d, -4, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

WHEN 6 THEN DATEADD(d, -5, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

ELSE DATEADD(d, -6, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

END

THEN 1

-- Calculate the ISO week number for all dates that are not part of the exceptions above

ELSE (DATEDIFF(d,

CASE (DATEPART(dw, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) + @@DATEFIRST - 1) % 7

WHEN 1 THEN CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)

WHEN 2 THEN DATEADD(d, -1, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

WHEN 3 THEN DATEADD(d, -2, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

WHEN 4 THEN DATEADD(d, -3, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

WHEN 5 THEN DATEADD(d, -4, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

WHEN 6 THEN DATEADD(d, -5, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

ELSE DATEADD(d, -6, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

END,

@date) / 7) + 1

END AS IsoWeek