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 @iDayOfYear4

    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))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))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