Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

ISO Week or why the datepart TSQL function will not work with weeks

Posted by thomasivarssonmalmo on June 4, 2008

If you try to build a week-year hierarchy in your time dimension and have decided to use the TSQL Datepart-function, that has a week argument, you will get a very strange week classification. The issue is how weeks roll up to years.
Here in Scandinavia we use the classification of the ISO WEEK. I am sure that it is used in many other countries as well.
In SQL Server 2005 you will have to build a TSQL User Defined Function(UDF) to solve this business problem.
Create Function dbo.IsoWeek(@Date DateTime)
Returns Int
 Declare @IsoWeek int
 Set @IsoWeek = Datepart(wk,@Date)+1 -DatePart(wk,Cast(Datepart(yy,@Date) As Char(4))+ ‘0104’)
–Special case: Jan 1-3 might belong to the previous year
 IF(@IsoWeek = 0)
 Set @IsoWeek=dbo.IsoWeek(Cast(Datepart(yy,@Date)-1 As Char(4))+’12’ + Cast(24+Datepart(Day,@Date) As Char(2)))+1
–Special case: Dec 29-31 might belong to the next year
 IF((Datepart(mm,@Date)=12) AND
 Set @IsoWeek=1
RETURN (@IsoWeek)
The source is the TSQL programming book that I have recommended here.
To try this function after it is succesfully created you can try these selects and compare the results with the datepart function.
Select dbo.IsoWeek(Getdate())
Select dbo.IsoWeek(‘2005-12-31′),DateName(weekday,’2005-12-31’) –saturday
Select dbo.IsoWeek(‘2004-12-31′), DateName(weekday,’2004-12-31’) –´friday
Select dbo.IsoWeek(‘2003-12-31′), DateName(weekday,’2003-12-31’)  –Wednesday
I recommend you to use an update statement in your time dimension. I have tried to call the function within BIDS and a named calculation but it will create an issue in the design environment.
If you run an update on your time dimension you can do it like this in SSIS.
Update MyTimeTime
Set MyIsoWeek = dbo.IsoWeek(MyDate) 
In SSAS2005 you can create a new hierarchy in the time dimension with date-week-year as the levels.
The good news is that SQL Server 2008 will have a new ISO Week argument for the datepart function that can solve this problem. I have not tried this and only found the link.
Edit: I have tried this in SQL Server 2008 RC0 with


 and it returns valid results.

3 Responses to “ISO Week or why the datepart TSQL function will not work with weeks”

  1. Kai said

    I was using the isoweek function for quite a while when I found that it returns different values depending on what the datefirst setting is on the system.
    set datefirst 1
    select dbo.isoWeek(‘20081230’) -> returns week no 1, this is correct

    the same with US setting:
    set datefirst 7
    select dbo.isoWeek(‘20081230’) -> returns week no 53, this is not correct, there is no week no 53 in 2008

    I guess it needs an adoption in the line that takes care for special day at the end of the year.



  2. try this query

    select DATEPART(ISO_WEEK, ‘2011-01-02 00:00:00.000’)

    returns 52

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: