SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


On the Trail of the ISO Week


On the Trail of the ISO Week

Author
Message
Adrian Peakman
Adrian Peakman
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 22
Hi,

The ISO Week problem arised last year for us. I programmed, tested and benchmarked a set of various conversion routines. Speed was essential in my case.
Above routine: 1360ms for 10000 conversions
fnISOWeek: 513ms for 10000 conversions

In general I first group results by SQL Year/Week then convert the grouped results using fnISOWeekFromSQL (very fast). Finally I regroup, as SQL separates the end and beginning weeks over a year end. This is faster in my case (millions of rows) than converting every row, especially in crosstabs.

I thought I'd share them, though please do credit me if you use them!:

fnISOWeek (@datetime) returns ISO week as an integer from SQL datetime
fnISOYear (@datetime) returns ISO Year as an integer from SQL datetime
fnISOYearWeek (@datetime) Returns the ISO Year Week in YYYYWW format of the given SQL date (useful for ordering data)

fnISOWeekFromSQL (@week, @year) returns ISO week given the SQL DATEPART week/year
fnISOYearWeekFromSQL (@week, @year) Returns the ISO Year Week in YYYYWW format given the SQL DATEPART Year and Week

fnISOWeekStartDate (@week, @year) Returns the date of the first day of the given ISO Week/Year (Monday)


As we're talking date functions, I also have a very fast midnight calculation if anybody is interested (calculates "12/1/2006 00:00:00" from "12/1/2006 xx:xx:xx")

Adrian Peakman



PS (Added)
Oh, the use of DATEPART(dw) is sensitive to the value of DATEFIRST. (I think everyone's routines are affected by this... )

My routines are coded to use the ISO Standard of Monday as the first day of the week (SET DATEFIRST 1). US installation is DATEFIRST 7 (Sunday) by default I believe. They can easily be altered to conform to other requirements.


---------------------------------------
CREATE FUNCTION fnISOWeek
--Adrian Peakman 6/12/2004
--Returns the ISO Week of the given date
(@d datetime)
returns int
AS
BEGIN
DECLARE @ISOWeek int

SET @ISOWeek= DATEPART(wk,@d)

IF DATEPART(dw,CAST(DATEPART(Year,@d) as varchar)) >4
begin
SET @ISOWeek=@ISOWeek-1
IF @ISOWeek=0
begin
SET @d = DATEADD(day,-3,@d)
SET @ISOWeek= DATEPART(wk,@d)
IF DATEPART(dw,CAST(DATEPART(Year,@d) as varchar)) >4
SET @ISOWeek=@ISOWeek-1
end
end

IF @ISOWeek=53
IF (DATEPART(dd,@d)-DATEPART(dw,@d))>= 28 SET @ISOweek=1

Return (@ISOWeek)
END
--------------------------------
CREATE FUNCTION fnISOYear
--Adrian Peakman 6/12/2004
--Returns the ISO Year of the given date
(@d datetime)
returns int
AS
BEGIN
DECLARE @ISOWeek int
DECLARE @iDayOfYear int
DECLARE @iYear int

SET @iDayOfYear=DATEPART(dy,@d)
SET @iYear=DATEPART(Year,@d)

IF @iDayOfYear > 3 AND @iDayOfYear < 363 RETURN(@iYear)

--Okay now this date may be part of the current year, the last year or the next year!

SET @ISOWeek= dbo.fnISOWeek(@d)

IF @iDayOfYear<10
begin
--Start of Year
IF @ISOWeek=1 RETURN(@iYear)
RETURN(@iYear-1)
end

--End Of Year
IF @ISOWeek=1 RETURN(@iYear+1)

RETURN(@iYear)

END
---------------------
CREATE FUNCTION fnISOYearWeek
--Adrian Peakman 6/12/2004
--Returns the ISO Year Week in YYYYWW format of the given date
(
@d datetime
)
RETURNS int
As

BEGIN
DECLARE @ISOWeek int,
@ISOYear int

SET @ISOWeek= DATEPART(wk,@d)
SET @ISOYear= DATEPART(Year,@d)

IF DATEPART(dw,CAST(@ISOYear as varchar)) >4
begin
SET @ISOWeek=@ISOWeek-1
IF @ISOWeek=0
begin
--SET @ISOWeek=dbo.fnISOWeek(DATEADD(day,-3,@d))
SET @d = DATEADD(day,-3,@d)
SET @ISOWeek= DATEPART(wk,@d)
IF DATEPART(dw,CAST(DATEPART(Year,@d) as varchar)) >4
SET @ISOWeek=@ISOWeek-1

IF @ISOWeek >0 SET @ISOYear=@ISOYear-1
end
end

IF @ISOWeek=53
IF (DATEPART(dd,@d)-DATEPART(dw,@d))>= 28
BEGIN
SET @ISOweek=1
SET @ISOYear=@ISOYear+1
END


RETURN(@ISOYear*100+@ISOWeek)

END
-------------------------------
CREATE FUNCTION fnISOWeekFromSQL
--Adrian Peakman 6/12/2004
--Returns the ISO Week given the SQL Week
(
@IntWeekNumber int,
@IntYear int
)
RETURNS int
As

BEGIN
DECLARE @ISOWeek int
DECLARE @Jan1Day datetime

SET @Jan1Day=DATEPART(dw,CONVERT(varchar,@IntYear))

SET @ISOWeek=@IntWeekNumber

IF @Jan1Day>4
SET @ISOWeek=@ISOWeek-1

IF @ISOWeek>52
begin
IF DATEPART(dw,CONVERT(varchar,@IntYear+1))<5
SET @ISOWeek=1
end

IF @ISOWeek>0
RETURN(@IsoWeek)

SET @ISOWeek=53
IF DATEPART(dw,CONVERT(varchar,@IntYear-1)) >4
SET @ISOWeek=@ISOWeek-1

RETURN(@ISOWeek)

END
---------------------------------
CREATE FUNCTION fnISOYearWeekFromSQL
--Adrian Peakman 6/12/2004
--Returns the ISO Year Week in YYYYWW format given the SQL DATEPART Year and Week
(
@intWeekNumber int,
@intYear int
)
RETURNS int
As

BEGIN
DECLARE @ISOWeek int
DECLARE @ISOYear int
DECLARE @Jan1Day datetime

SET @Jan1Day=DATEPART(dw,CONVERT(varchar,@intYear))

SET @ISOWeek=@intWeekNumber
SET @ISOYear=@intYear

IF @Jan1Day>4
SET @ISOWeek=@ISOWeek-1

IF @ISOWeek>52
begin
IF DATEPART(dw,CONVERT(varchar,@intYear+1))<5
begin
SET @ISOWeek=1
SET @ISOYear=@ISOYear+1
end
end

IF @ISOWeek>0
RETURN(@ISOYear*100+@IsoWeek)

SET @ISOWeek=53
SET @ISOYear=@ISOYear-1

IF DATEPART(dw,CONVERT(varchar,@IntYear-1)) >4
begin
SET @ISOWeek=@ISOWeek-1
end

RETURN(@ISOYear*100+@ISOWeek)

END
----------------------------------
CREATE FUNCTION fnISOWeekStartDate
--Adrian Peakman 6/12/2004
--Returns the date of the first day of the given ISO Week/Year (Monday)
(
@IntWeekNumber int,
@IntYear int
)
RETURNS DateTime
As

BEGIN
DECLARE @StartDate DateTime
DECLARE @Jan1Day int

SET @StartDate=CONVERT(varchar,@IntYear)
SET @Jan1Day=DATEPART(dw,@StartDate)

IF @Jan1Day>4
Set @StartDate=DATEADD(day,1-@Jan1Day+7*@IntWeekNumber,@StartDate)
ELSE
Set @StartDate=DATEADD(day,1-@Jan1Day+7*(@IntWeekNumber-1),@StartDate)

RETURN(@StartDate)

END
Adrian Peakman
Adrian Peakman
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 22
Incidentally the routine posted by rkintrup fails for me every few years:
Can't test Tomasz's routine as if fails on my installation (British dates). I'd be interested to know if my routines fail on other installations than British, hopefully they shouldn't! (erk!)

Oh, the use of DATEPART(dw) is sensitive to the value of DATEFIRST. (I think everyone's routines are affected by this... )

My routines are coded to use the ISO Standard of Monday as the first day of the week (SET DATEFIRST 1). US installation is DATEFIRST 7 (Sunday) by default I believe. They can easily be altered to conform to other requirements.

Thanks,

Adrian

Date fnISOWeek Rkintrup
002/01/1970 1 53
09/01/1970 2 54
16/01/1970 3 55
23/01/1970 4 56
30/01/1970 5 57
06/02/1970 6 58
13/02/1970 7 59
20/02/1970 8 60
27/02/1970 9 61
06/03/1970 10 62
13/03/1970 11 63
20/03/1970 12 64
02/01/1976 1 53
09/01/1976 2 54
16/01/1976 3 55
23/01/1976 4 56
30/01/1976 5 57
06/02/1976 6 58
13/02/1976 7 59
20/02/1976 8 60
27/02/1976 9 61
05/03/1976 10 62
12/03/1976 11 63
19/03/1976 12 64
02/01/1981 1 53
09/01/1981 2 54
16/01/1981 3 55
23/01/1981 4 56
30/01/1981 5 57
06/02/1981 6 58
13/02/1981 7 59
20/02/1981 8 60
27/02/1981 9 61
06/03/1981 10 62
13/03/1981 11 63
20/03/1981 12 64
02/01/1987 1 53
09/01/1987 2 54
16/01/1987 3 55
23/01/1987 4 56
30/01/1987 5 57
06/02/1987 6 58
13/02/1987 7 59
20/02/1987 8 60
27/02/1987 9 61
06/03/1987 10 62
13/03/1987 11 63
20/03/1987 12 64
02/01/1998 1 53
09/01/1998 2 54
16/01/1998 3 55
23/01/1998 4 56
30/01/1998 5 57
06/02/1998 6 58
13/02/1998 7 59
20/02/1998 8 60
27/02/1998 9 61
06/03/1998 10 62
13/03/1998 11 63
20/03/1998 12 64
02/01/2004 1 53
09/01/2004 2 54
16/01/2004 3 55
23/01/2004 4 56
30/01/2004 5 57
06/02/2004 6 58
13/02/2004 7 59
20/02/2004 8 60
27/02/2004 9 61
05/03/2004 10 62
12/03/2004 11 63
19/03/2004 12 64
02/01/2009 1 53
09/01/2009 2 54
16/01/2009 3 55
23/01/2009 4 56
30/01/2009 5 57
06/02/2009 6 58
13/02/2009 7 59
20/02/2009 8 60
27/02/2009 9 61
06/03/2009 10 62
13/03/2009 11 63
20/03/2009 12 64
02/01/2015 1 53
09/01/2015 2 54
16/01/2015 3 55
23/01/2015 4 56
30/01/2015 5 57
06/02/2015 6 58
13/02/2015 7 59
20/02/2015 8 60
27/02/2015 9 61
06/03/2015 10 62
13/03/2015 11 63
20/03/2015 12 64
Bill the Cat
Bill the Cat
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 Visits: 3
FYI, the article sites ISO6801, I believe the author is referring to ISO8601, and I have certainly run into this same kind of issue in respect to ensuring applications developed in different environments are compatible with 8601 defined week numbers and weekday numbers.



Chris Hedgate
Chris Hedgate
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3139 Visits: 7
Where does it say ISO 6801 in the article? I can't find any references to it. I know I had an error a long time ago, but that was changed to 8601.

Re: the setting of DATEFIRST. Note this section from my article:

First you should note that whenever the day of the week is checked for a specific date, we always make sure that the ISO standard of using Monday as the starting day of the week is used, regardless of the current setting of @@datefirst. This is accomplished by adding @@datefirst - 1 to the value returned by DATEPART(dw, ) and then dividing this number by 7. The remainder of the division is the day of the week for the specified date using Monday as the starting day of the week. Note though that Sunday becomes 0 with this expression, so if you need it to be 7 (for some calculation or whatever) you must exchange it for that.

Finally, regarding performance, the fastest way I have found to get ISO 8601 week numbers is to use the built-in routines in Visual Basic.NET. Creating a user-defined function in Visual Basic.NET is very easy, so if you're running SQL Server 2005 that is an option.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/
Ian Cox-160558
Ian Cox-160558
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 4
I like your method Tomasz, it leads to much cleaner looking queries.
However, your code doesn't quite work.

In particular you are subtracting 1 from the day of the week twice
This modified version works

CREATE FUNCTION dbo.fnISOweek(@date datetime)
RETURNS int
AS
BEGIN

declare @dayOfWeek int
declare @1ThISOWeek1ThDay datetime
declare @4ThJan datetime
declare @1StJan datetime
declare @31StDec datetime

declare @numberOfISOweeks int
declare @tmp int
declare @returnValue as int

set @4ThJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '0104' as datetime)
set @1StJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '0101' as datetime)
set @31StDec = cast(CAST(YEAR(@date) AS CHAR(4)) + '1231' as datetime)

--"rule of thursday"
if (DATEPART(dw, @1StJan) = 5 or DATEPART(dw, @31StDec) = 5 )
set @numberOfISOweeks = 53
else
set @numberOfISOweeks = 52

set @dayOfWeek = DATEPART(dw, @4ThJan)

set @1ThISOWeek1ThDay = DATEADD(day, -(@dayOfWeek -1) ,@4ThJan)

if @date < @1ThISOWeek1ThDay
BEGIN
if (DATEPART(dw, DATEADD(year,-1, @1StJan)) = 5 or DATEPART(dw,DATEADD(year,-1, @31StDec)) = 5 )
set @returnValue = 53
else
set @returnValue = 52

return @returnValue

END

--number of days between 1th day of the 1th ISO week and Jan 1

set @tmp = (select DATEDIFF(day,@1StJan , @1ThISOWeek1ThDay)) + 1

set @returnValue = (DATEPART(dy,@date)- @tmp ) / 7 + 1

-- if number of week is greater than number of ISO weeks in current year it means
-- that the date is in the first week of next year

if @returnValue > @numberOfISOweeks
set @returnValue = 1 -- or -1 as an error

return @returnValue

END
unisense_nl
unisense_nl
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 1

Maybe, other people like this function
GO
CREATE FUNCTION [dbo].[GetDateTimeOfIsoWeek](@WeekYearNumber int, @DayNumber int)
RETURNS datetime
AS
------------------------------------------------------------------------------
-- GetDateTimeOfIsoWeek
--
-- According to ISO 8601, the first week of the year (week 1) is the week
-- that contains at least the first four days of the year. In other words,
-- the week that contains the first Thursday of a year is also week 1 of that year.
-- Also note that according to ISO 8601 a week always starts on a Monday.
-- if 01-01=Monday then 01-04=Thursday -> 01-01=week 1 AND 01-04=week 1
-- if 01-01=Thuesday then 01-04=Friday -> 01-01=week 1 AND 01-04=week 1
-- if 01-01=Wednesday then 01-04=Saterday -> 01-01=week 1 AND 01-04=week 1
-- if 01-01=Thursday then 01-04=Sunday -> 01-01=week 1 AND 01-04=week 1
-- if 01-01=Friday then 01-04=Monday -> 01-01=week 52 AND 01-04=week 1
-- if 01-01=Saterday then 01-04=Thuesday -> 01-01=week 52 AND 01-04=week 1
-- if 01-01=Sunday then 01-04=Wednesday-> 01-01=week 52 AND 01-04=week 1
--
-- 4th jan. of indicated year is always in week 1
-- week 1 always starts on Monday, just like other weeks
-- 4th jan. must be Thursday, Friday, Saterday or Sunday if 01-01 is in week 1
-- 1th jan. must be Monday, Thuesday, Wednesday or Thursday if 01-01 is in week 1
--
------------------------------------------------------------------------------
-- Created On: 19 december 2006
-- Created By: martijn Schuurmans
-- Variables:
-- * @WeekYearNumber:
-- like 200752, 200453, etc.
-- * @DayNumber:
-- 1 = Monday
-- 2 = Thuesday
-- 3 = Wednesday
-- 4 = Thursday
-- 5 = Friday
-- 6 = Saterday
-- 7 = Sunday
------------------------------------------------------------------------------
BEGIN
-- Declare the return variable here
DECLARE @DayId int
DECLARE @WeekNumber int
DECLARE @YearNumber int
DECLARE @FunctionResult datetime

-- validate input, we don't like negative WeekYear numbers
SET @WeekYearNumber = ABS(@WeekYearNumber)
IF @DayNumber < 1
SET @DayNumber = 1

IF @DayNumber > 7
SET @DayNumber = 7

-- get WeekId without Year Number
SET @WeekNumber = right(@WeekYearNumber, 2)

-- get YearId from @YearNumber
SET @YearNumber =
CASE
-- like: 52
WHEN @WeekYearNumber < 100 THEN YEAR(GETDATE())
-- like: 0752
WHEN @WeekYearNumber < 10000 THEN (LEFT(YEAR(GETDATE()),2)*100) + left(@WeekYearNumber, 2)
-- like: 200752
ELSE LEFT(@WeekYearNumber, 4)
END

-- get datevalue of monday in week 1 of indicated year
-- get datevalue of 1th jan. of indicated year
SET @FunctionResult = CAST((CAST(@YearNumber AS char(4))+ '0101') AS datetime)
-- determine day of week for 01-01-YearNumber
-- make corrections for server settings (=@@DATEFIRST)
SET @DayId = DATEPART(dw, @FunctionResult) + @@DATEFIRST - 1
IF @DayId > 7
SET @DayId = @DayId - 7

-- determine the correction that is needed on 01-01-YearNumber day to get to Monday of week 1
-- @DayId: 1=Monday; 2=Thuesday; 3=Wednesday; 4=Thursday; 5=Friday; 6=Saterday; 7=Sunday
SET @DayId =
CASE @DayId
WHEN 1 THEN 0 -- if weekday of 01-01=Monday then: no correction
WHEN 2 THEN -1 -- if weekday of 01-01=Thuesday then: Monday of W1 is 1 day before 01-01
WHEN 3 THEN -2 -- if weekday of 01-01=Wednesday then: Monday of W1 is 2 days before 01-01
WHEN 4 THEN -3 -- if weekday of 01-01=Thursday then: Monday of W1 is 3 days before 01-01
WHEN 5 THEN 3 -- if weekday of 01-01=Friday then: Monday of W1 is 3 days after 01-01
WHEN 6 THEN 2 -- if weekday of 01-01=Saterday then: Monday of W1 is 2 days after 01-01
WHEN 7 THEN 1 -- if weekday of 01-01=Sunday then: Monday of W1 is 1 day after 01-01
END

-- this is datevalue of monday in week 1 of indicated year
SET @FunctionResult = DATEADD(dd, @DayId, @FunctionResult)

-- get last day of week, this is always sunday
-- and make correction for given @DayNumer
SET @FunctionResult = DATEADD(dd, ((@WeekNumber * 7) + @DayNumber - 8), @FunctionResult)

RETURN(@FunctionResult)
END


ms-876980
ms-876980
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
Hi,
I've found this ISO-Week function everywhere on the NET, but It isn't correct. Try with 2008.08.31. It must be week35 instead of week36. Has anybody in the world a right fuction for this ????
Adrian Peakman
Adrian Peakman
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 22
Yes... mine work (see earlier in the thread)

print dbo.fnISOYearWeek('Aug 31 2008')

Gives 200835 BigGrin
karol63
karol63
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 10
Perfect, thanky you.

It helped me a lot.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114945 Visits: 41399
Lookup CREATE FUNCTION in Books Online... Example "A" is a nice, short function that determines ISO week. It could probably be optimized to be an INLINE function for performance reasons or might be able to be optimized to simply be a formula instead of a function, but it's a whole lot shorter and easier to understand than some of the example code I've seen on this thread.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search