# On the Trail of the ISO Week

• Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/chedgate/onthetrailoftheisoweek.asp

• I have some problems when I try to implement this solution. I get results in the fourties and up. When I eg. input 2005-01-27 a normal datepart with wk gives the result 5, but this function gives the result 44.

Declare @startdate as smalldatetime

set @startdate ='2005-27-01'

SELECT @startdate,DATEPART(wk, @startdate) as Sql_weeknr,

CASE

-- Exception where @startdate is part of week 52 (or 53) of the previous year

WHEN @startdate <

CASE (DATEPART(dw, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7

WHEN 1 THEN CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04'

WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

ELSE DATEADD(d, -6, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

END

THEN

(DATEDIFF(d,

CASE (DATEPART(dw, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7

WHEN 1 THEN CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04'

WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')

WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')

WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')

WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')

WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')

ELSE DATEADD(d, -6, CAST(YEAR(@startdate) - 1 AS CHAR(4)) + '-01-04')

END,

@startdate

) / 7) + 1

-- Exception where @startdate is part of week 1 of the following year

WHEN @startdate >= CASE (DATEPART(dw, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7

WHEN 1 THEN CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04'

WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')

WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')

WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')

WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')

WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')

ELSE DATEADD(d, -6, CAST(YEAR(@startdate) + 1 AS CHAR(4)) + '-01-04')

END

THEN 1

ELSE

-- Calculate the ISO week number for all dates that are not part of the exceptions above

(DATEDIFF(d,

CASE (DATEPART(dw, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7

WHEN 1 THEN CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04'

WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

ELSE DATEADD(d, -6, CAST(YEAR(@startdate) AS CHAR(4)) + '-01-04')

END,

@startdate

) / 7) + 1

END AS IsoWeek

Best regards

John Valore

• I tried John's code - I had to change the @StartDate to '2005-01-27'.

I had no problems - I received an ISO Week of "4"

Regards,

Nic Washington

• Thanks for the reply Nic,

I took some time before someone responded, so I had actually abandoned the approach and settled with the standard datepart function.

Thanks to your response, I looked at the problem again and have now solved the problem. I still have to input the date in the format yyyy-mm-dd :

set @startdate =CAST('2005-01-27' as smalldatetime)

but if I include a statement :

set dateformat mdy

(default american date input/output), the code from Chris Hedgate now works.

The devil is apparently still in the details and the several datetime formats.

Thank you very much.

John Valore

• Hi,

Just an update.

I was going to use the functionality in an udf environment, but "forgot" that it is not possible to issue SET commands in an udf.

So I had to look at Chris code again when it suddenly strooke me, that I should replace all the '-01-04' with '-04-01'.

Bingo.

John Valore

• Cris:

If you use the ISO format for dates (yyyymmdd), not the yyyy-mm-dd format, SQL Server is not vulnerable for myd or dmy dates.

See CONVERT ISO 112 (or 12 for yymmdd) in BOL.

SQL server accepts '20050323' (today, y=2005, m=03, d=23) either in Europe or USA.

• Here is a version which uses CONVERT to allow using the yyyy-mm-dd format. This makes the code independent from SQL Server installation flavors.

Ralf

-----

DECLARE @date datetime

SET @date = CONVERT(datetime, '2003-12-31', 120) -- 1

--SET @date = CONVERT(datetime, '2005-01-01', 120) -- 53

--SET @date = CONVERT(datetime, '2005-01-05', 120) -- 1

--SET @date = CONVERT(datetime, '2006-01-01', 120) -- 52

SELECT @date,

CASE

-- Exception where @date is part of week 52 (or 53) of the previous year

WHEN @date =

CASE (DATEPART(dw, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)) + @@DATEFIRST - 1) % 7

WHEN 1 THEN CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120)

WHEN 2 THEN DATEADD(d, -1, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

WHEN 3 THEN DATEADD(d, -2, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

WHEN 4 THEN DATEADD(d, -3, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

WHEN 5 THEN DATEADD(d, -4, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

WHEN 6 THEN DATEADD(d, -5, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

ELSE DATEADD(d, -6, CONVERT(datetime, CAST(YEAR(@date) + 1 AS CHAR(4)) + '-01-04', 120))

END

THEN 1

-- Calculate the ISO week number for all dates that are not part of the exceptions above

ELSE (DATEDIFF(d,

CASE (DATEPART(dw, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)) + @@DATEFIRST - 1) % 7

WHEN 1 THEN CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120)

WHEN 2 THEN DATEADD(d, -1, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

WHEN 3 THEN DATEADD(d, -2, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

WHEN 4 THEN DATEADD(d, -3, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

WHEN 5 THEN DATEADD(d, -4, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

WHEN 6 THEN DATEADD(d, -5, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

ELSE DATEADD(d, -6, CONVERT(datetime, CAST(YEAR(@date) AS CHAR(4)) + '-01-04', 120))

END,

@date) / 7) + 1

END AS IsoWeek

• Hmmm that is my function similiar to above but IMHO easier to understand

CREATE FUNCTION ISOweek(@date datetime)

RETURNS int

AS

BEGIN

declare @dayOfWeek int

declare @1ThISOWeek1ThDay datetime

declare @4ThJan datetime

declare @1ThJan datetime

declare @31ThDec datetime

declare @numberOfISOweeks int

declare @Tmp int

declare @returnValue as int

set @4ThJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '-01-04' as datetime)

set @1ThJan = cast(CAST(YEAR(@date) AS CHAR(4)) + '-01-01' as datetime)

set @31ThDec = cast(CAST(YEAR(@date) AS CHAR(4)) + '-12-31' as datetime)

--"rule of thursday"

if (DATEPART(dw, @1ThJan) = 5 or DATEPART(dw, @31ThDec) = 5 )

set @numberOfISOweeks = 53

else

set @numberOfISOweeks = 52

set @dayOfWeek = (select DATEPART(dw, @4ThJan) - 1)

if @dayOfWeek = 0

set @dayOfWeek = 7

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

if @date @numberOfISOweeks

set @returnValue = 1 -- or -1 as an error

return @returnValue

END

• Sombody now how to calculate first monday day from given week

For example I need get the day from a given week:

ISO date ‘20050101’ is the week 53 in ISO 8601

If a have the week number 53 how I can calculate the ISO date?

• Better late than never, I guess...

DECLARE @Year    CHAR(4)

DECLARE @NextYear CHAR(4)

DECLARE @ISOWeek VARCHAR(2)

SET @Year    = '2001'

SET @ISOWeek = '53'

SET @NextYear = @Year+1

SELECT

CASE

WHEN @ISOWeek > 0

ELSE 0

END AS StartDate,

--todo... needs some error checking for the week number...

It returns a "0" or 01/01/1900 for the start date if the week was not found for the year...

I'm thinking that there's no ISO week 53 in 2001 but I might be using the wrong ISO "standard".

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

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

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

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

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

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

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

ELSE

RETURN(@StartDate)

END

• 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,

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

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

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

• 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 @numberOfISOweeks

set @returnValue = 1 -- or -1 as an error

return @returnValue

END

Viewing 15 posts - 1 through 15 (of 23 total)