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
4 comments
Hide comments