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

by

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

More Related Contents:

  1. Comma separated results in SQL
  2. T-SQL stored procedure that accepts multiple Id values
  3. Understanding PIVOT function in T-SQL
  4. How do I escape a percentage sign in T-SQL?
  5. Using RegEx in SQL Server
  6. Fuzzy matching using T-SQL
  7. How do you count the number of occurrences of a certain substring in a SQL varchar?
  8. Find index of last occurrence of a sub-string using T-SQL
  9. Hidden Features of SQL Server
  10. SQL Server 2008 Empty String vs. Space
  11. How do I use the CONCAT function in SQL Server 2008 R2?
  12. EXEC sp_executesql with multiple parameters
  13. SQL Server: combining multiple rows into one row
  14. Query to list all stored procedures
  15. SQL Server: Index columns used in like?
  16. WHERE clause on SQL Server “Text” data type
  17. Return all possible combinations of values on columns in SQL
  18. SQL Server error on update command – “A severe error occurred on the current command”
  19. Recursive sum in tree structure
  20. SQL server schema and default schema
  21. SQL to find first non-numeric character in a string
  22. SQL Server – Auto-incrementation that allows UPDATE statements
  23. Compute MD5 hash of a UTF8 string
  24. LPAD in SQL Server 2008
  25. How to check SQL Server Database compatibility after sp_dbcmptlevel is deprecated?
  26. How do I remove non-breaking spaces from a column in SQL server?
  27. SQL SERVER – Understanding how MIN(text) works
  28. Pivot multiple columns based on one column in SQL Server
  29. @@ERROR and/or TRY – CATCH
  30. How to calculate difference in hours (decimal) between two dates in SQL Server?

Leave a Comment