I have some problems when I try to implement this solution. I get results in the fourties and up. When I eg. input 2005-01-27 a normal datepart with wk gives the result 5, but this function gives the result 44.

Declare @startdate as smalldatetime

set @startdate ='2005-27-01'

SELECT @startdate,DATEPART(wk, @startdate) as Sql_weeknr,

CASE

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

WHEN @startdate <

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

WHEN 1 THEN CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04'

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

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

WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

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

ELSE DATEADD(d, -6, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

END

THEN

(DATEDIFF(d,

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

WHEN 1 THEN CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04'

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

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

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

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

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

ELSE DATEADD(d, -6, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')

END,

@startdate

) / 7) + 1

-- Exception where @startdate is part of week 1 of the following year

WHEN @startdate >= CASE (DATEPART(dw, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7

WHEN 1 THEN CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04'

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

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

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

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

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

ELSE DATEADD(d, -6, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')

END

THEN 1

ELSE

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

(DATEDIFF(d,

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

WHEN 1 THEN CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04'

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

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

WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

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

ELSE DATEADD(d, -6, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

END,

@startdate

) / 7) + 1

END AS IsoWeek

A bit puzled about this 

Best regards

John Valore