16 December 2010

Getting the ISO weeknumber in SQL Server 2005 (and earlier)

I just discovered that SQL Server often returns the wrong week number when using DatePart(wk, @date).
For instance, SELECT DatePart(wk, '2010-12-17') returns 47 while it should be 46.
This is because SQL Server starts counting weeks from 1 Jan, so 1 Jan is always in week 1 of the year.
The ISO standard states that week 1 is the first week with 4 days in it.
The following code can be used (with @date being the datetime) to return the ISO week

CREATE FUNCTION GetISOWeek(@date DateTime)
RETURNS INTEGER
AS
BEGIN

  declare @ISOweek INTEGER;
  select @ISOweek = datepart(wk ,@date) + 1 - 

    datepart(wk, 'Jan 4,' CAST(datepart(yy, @date) as CHAR(4)));
  if (@ISOweek = 0)
    select @ISOweek = datepart(wk, 'Dec ' + CAST(24 + 

      datepart(day, @date) AS CHAR(2)) + ','
      CAST(datepart(yy, @date) - 1 as CHAR(4))) + 1
  RETURN @ISOweek
END
GO

(From windowsitpro.com)

Since SQL2008, DatePart supports the ISO Week, by calling:
DatePart(isowk, @date) or  DatePart(isoww, @date)

3 comments :

Anonymous said...

This was very helpful. Thanks a lot

Anonymous said...

this script like many other isoweek scripts fails.

These dates gives the wrong result.

2010-12-26
2011-01-01
2011-01-02
2011-01-09
2011-01-16
2011-01-23
2011-01-30
2011-02-06
2011-02-13
2011-02-20
2011-02-27
2011-03-06
2011-03-13
2011-03-20
2011-03-27
2011-04-03
2011-04-10
2011-04-17
2011-04-24
2011-05-01
2011-05-08
2011-05-15
2011-05-22
2011-05-29
2011-06-05
2011-06-12
2011-06-19
2011-06-26
2011-07-03
2011-07-10
2011-07-17
2011-07-24
2011-07-31
2011-08-07
2011-08-14
2011-08-21
2011-08-28
2011-09-04
2011-09-11
2011-09-18
2011-09-25
2011-10-02
2011-10-09
2011-10-16
2011-10-23
2011-10-30
2011-11-06
2011-11-13
2011-11-20
2011-11-27
2011-12-04
2011-12-11
2011-12-18
2011-12-25
2012-01-01

Michael Jepson said...

Dear anonymous,

As you have probably also noticed yourself, all the problems you state are sundays. It all depends on what day is considered to be the first day of the week for your locale. For mine it is monday, leaving sunday as the last day of the week.
I'm sure you can come up with a fix for this, as it just needs a shift of a day. You might even consider doing a DateAdd(d, @var, 1) (or -1, not sure atm) before running the rest of the code to compensate for this.

Please let me know if you need any further assistance.

Regards,

Michael