Getting week number off a date in MS SQL Server 20

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

have you considered using the WEEK function?

This will get you the week of the year for the specified date that you pass in.

SELECT { fn WEEK(GETDATE()) } AS WeekNumber, { fn WEEK(CONVERT(DATETIME, '2008-01-01 00:00:00', 102)) } AS FirstWeekOfYear, { fn WEEK(CONVERT(DATETIME, '2008-12-31 00:00:00', 102)) } AS LastWeekOfYear

This outputs the following SQL2000 and SQL2005:

• WeekNumber: 50
• FirstWeekOfYear: 1
• LastWeekOfYear: 53

I Hope this helps :)

Answer 2

FORGET THE OTHER ANSWERS

The question specifies "the week starts monday and week 1 is the first week of the year with atleast 4 days." This is ISO 8601 standard and what this answer provides. This function is used in production on our site.

This is all you need:

CREATE FUNCTION ISOweek  (@DATE DATETIME)
RETURNS INT
AS
BEGIN
    RETURN (datepart(DY, datediff(d, 0, @DATE) / 7 * 7 + 3)+6) / 7
END
GO

Answer 3

This will return you the week number of date entered in quotes

SELECT DATEPART( wk, 'enter the date over here' )

Answer 4

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

Answer 5

Looks like the DATEPART mssql function should help you out with ...

DATEPART(wk, ‘Jan 1, xxxx’) = 1

Well I'll be.. turns out there is a way to set the first day of the week, DATEFIRST

SET DATEFIRST 1 -- for monday

Update: Now I understand better, what the OP wants.. which is custom-logic for this. I don't think MSSQL would have functions with such rich level of customization. But I may be wrong... I think you'll have to roll your own UDF here...sorry

Answer 6

Why yet again, people make mountains out of mole-hills, it astounds me?

So simple...

select DATEPART(wk, GETDATE())