On the Trail of the ISO Week

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Christoffer Hedgate... » On the Trail of the ISO Week

24 posts, Page 2 of 3

««1 2 3 »»

On the Trail of the ISO Week

Rate Topic

Display Mode

Topic Options

Author

Message

Adrian Peakman

Posted Friday, January 20, 2006 2:09 AM

SSC 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

Adrian Peakman

Posted Friday, January 20, 2006 2:54 AM

SSC 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

Bill the Cat

Posted Friday, January 20, 2006 8:04 AM

Mr 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

Chris Hedgate

Posted Monday, January 23, 2006 5:51 AM

UDP 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

Ian Cox-160558

Posted Thursday, July 13, 2006 8:02 AM

Forum 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

unisense_nl

Posted Tuesday, February 13, 2007 9:32 AM

Forum 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

ms-876980

Posted Thursday, October 09, 2008 7:22 AM

Forum 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

Adrian Peakman

Posted Thursday, October 09, 2008 8:48 AM

SSC 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

karol63

Posted Monday, March 22, 2010 8:41 AM

Forum 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

Jeff Moden

Posted Monday, March 22, 2010 11:28 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,906, Visits: 26,790

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #887528

« Prev Topic | Next Topic »

24 posts, Page 2 of 3

««1 2 3 »»

Permissions