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 :
This was very helpful. Thanks a lot
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
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
Post a Comment