tsql - Getting week number off a date in MS SQL Server 2005?

Question

Is it possible to create an sql statement that selects the week number (NOT the day of week - or the day number in a week). I'm creating a view to select this extra information along with a couple of other fields and thus can not use a stored procedure. I'm aware that it's possible to create a UDF to do the trick, but if at all possible i'd rather only have to add a view to this database, than both a view and a function.

Any ideas? Also where i come from, the week starts monday and week 1 is the first week of the year with atleast 4 days.

Related:

How do I calculate the week number given a date?

Answer 1

Be aware that there are differences in what is regarded the correct week number, depending on the culture. Week numbers depend on a couple of assumptions that differ from country to country, see Wikipedia article on the matter. There is an ISO standard (ISO 8601) that applies to week numbers.

The SQL server integrated DATEPART() function does not necessarily do The Right Thing. SQL Server assumes day 1 of week 1 would be January 1, for many applications that's wrong.

Calculating week numbers correctly is non-trivial, and different implementations can be found on the web. For example, there's an UDF that calculates the ISO week numbers from 1930-2030, being one among many others. You'll have to check what works for you.

This one is from Books Online (though you probably want to use the one from Jonas Lincoln's answer, the BOL version seems to be incorrect):

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
GO