Wrong week number using DATEPART in SQL Server

by

I’ve got the problem that

select datepart(ww, '20100208')

is returning as result week 7 in SQL Server 2000. But 08.02.2010 should be week 6 according to the ISO 8601 specification! This is causing problems in delivery week calculations.

What should I do to get week number values according to ISO 8601?

Answer

You can do this within SQL 2008 very easily as it now supports isoww as the first datepart argument. However, this wasn’t in SQL 2000 (or 2005). There is a function in this article which will do it for you in SQL 2000/2005.

In case the blog goes offline, here is the function. Go to the post to learn more about ISO and non-ISO weeks.

CREATE FUNCTION ISOweek  (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   SET @ISOweek= DATEPART(wk,@DATE)+1
      -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
   --Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0) 
      SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
         AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
   --Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND 
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END

Attribution
Source : Link , Question Author : MicSim , Answer Author : thomasb

Leave a Comment