For instance, SELECT DatePart(wk, '2010-12-17') returns 47 while it should be 46.
This is because SQL Server starts counting weeks from 1 Jan, so 1 Jan is always in week 1 of the year.
The ISO standard states that week 1 is the first week with 4 days in it.
The following code can be used (with @date being the datetime) to return the ISO week
CREATE FUNCTION GetISOWeek(@date DateTime)
declare @ISOweek INTEGER;
select @ISOweek = datepart(wk ,@date) + 1 -
datepart(wk, 'Jan 4,' + CAST(datepart(yy, @date) as CHAR(4)));
if (@ISOweek = 0)
select @ISOweek = datepart(wk, 'Dec ' + CAST(24 +
datepart(day, @date) AS CHAR(2)) + ',' +
CAST(datepart(yy, @date) - 1 as CHAR(4))) + 1
Since SQL2008, DatePart supports the ISO Week, by calling: