Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

On the Trail of the ISO Week Expand / Collapse
Author
Message
Posted Friday, January 20, 2006 2:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, October 28, 2012 7:01 AM
Points: 28, Visits: 17
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
Post #252251
Posted Friday, January 20, 2006 2:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, October 28, 2012 7:01 AM
Points: 28, Visits: 17
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
Post #252267
Posted Friday, January 20, 2006 8:04 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, December 15, 2011 3:47 PM
Points: 571, 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. 


Post #252377
Posted Monday, January 23, 2006 5:51 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Saturday, February 28, 2009 6:51 AM
Points: 1,489, 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/
Post #252763
Posted Thursday, July 13, 2006 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 04, 2008 7:13 AM
Points: 2, 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
Post #294100
Posted Tuesday, February 13, 2007 9:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 19, 2007 6:09 AM
Points: 2, 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

 

 

Post #344584
Posted Thursday, October 09, 2008 7:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 09, 2008 2:28 PM
Points: 1, 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 ????
Post #583295
Posted Thursday, October 09, 2008 8:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, October 28, 2012 7:01 AM
Points: 28, Visits: 17
Yes... mine work (see earlier in the thread)

print dbo.fnISOYearWeek('Aug 31 2008')

Gives 200835 :D
Post #583378
Posted Monday, March 22, 2010 8:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 05, 2012 5:02 AM
Points: 1, Visits: 10
Perfect, thanky you.

It helped me a lot.
Post #887415
Posted Monday, March 22, 2010 11:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 35,959, Visits: 30,250
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #887528
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse