Wrong week number using DATEPART in SQL Server

Question

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?

Sitzmark

Answer 1

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

Radom

Good blog post, indeed. :-) So there is no other way of getting the ISO week number in SQL Server 2000, except by using a UDF? – Sitzmark

@MicSim, unfortunately not. There is an argument to say don't do it in SQL Server, but return the data as-is and let your calling code work determine the week number. But that really depends on exact requirements so may or may not be worth you considering. – Radom

Answer 2

One easy way to do this is to use isowk instead of wk as shown here:

select datepart(isowk, '2010-02-08');

Like @MicSim and @AdaTheDev mentioned, this will only work in newer versions (>=2008).

Sanitarium

While this is correct for current SQL Server versions, the original question was about SQL Server 2000. The parameter you mention works only from version 2008 onwards. See AdaTheDev's answer, who already mentioned it. – Sitzmark

@Sitzmark you're right. My mistake. I answered in a hurry and didn't pay attention. Sorry. – Sanitarium

Was looking for this kind of parameter at datepart, thanks for this – Mittiemittimus