I like your method Tomasz, it leads to much cleaner looking queries.

However, your code doesn't quite work.

In particular you are subtracting 1 from the day of the week twice

This modified version works

CREATE FUNCTION dbo.fnISOweek(@date datetime)

RETURNS int

AS

BEGIN

declare @dayOfWeek int

declare @1ThISOWeek1ThDay datetime

declare @4ThJan datetime

declare @1StJan datetime

declare @31StDec datetime

declare @numberOfISOweeks int

declare @Tmp int

declare @returnValue as int

set @4ThJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '0104' as datetime)

set @1StJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '0101' as datetime)

set @31StDec = cast(CAST(YEAR(@date) AS CHAR(4)) + '1231' as datetime)

--"rule of thursday"

if (DATEPART(dw, @1StJan) = 5 or DATEPART(dw, @31StDec) = 5 )

set @numberOfISOweeks = 53

else

set @numberOfISOweeks = 52

set @dayOfWeek = DATEPART(dw, @4ThJan)

set @1ThISOWeek1ThDay = DATEADD(day, -(@dayOfWeek -1) ,@4ThJan)

if @date @numberOfISOweeks

set @returnValue = 1 -- or -1 as an error

return @returnValue

END