Why is the week number returned by SQL Server wrong? - 02 Nov 1999

A. e.g. select datepart(wk,'19990323') returns 13 when it should be 12.

This is because SQL Server starts counting weeks from Jan 1. Week 1 = Jan 1.

The ISO standard is that week 1 is the first week with 4 days in it.

The following code can be used (@date is the datetime) to return the ISO week

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