Microsoft introduced a correct ISO week number calculation in SQL Server 2008, so you can now easy calculate the ISO week number according to ISO-8601 using
Unfortunately, there are many production environments left that use earlier versions of SQL Server. For these environments, the only way to get the ISO week number is to write a custom function. As always there are better ways to do it and there are worse ways to do it.
Most of the functions I see are dependent on settings like SET DATEFIRST or SET LANGUAGE. I would like to have these codes removed, due to ambiguity. The functions may work on one server but not on another.
The simple solution looks like this and is built on an idea I had in 2009
Unfortunately, it is limited to date type DATETIME because Microsoft has not backported DATE in all their date features yet. Are you interested in seeing a complete ISO week function for all years between 1 and 9999 with no dependencies of built-in features, look here. However, since DATE is not available in SQL Server 2005 and earlier, we can do well with the code above.
So how does the algorithm work?
Week numbers according to ISO-8601 can be defined in different ways. Either the week (Monday to Sunday) containing the first Thursday (which is the formal definition) or the week that has at least four days in the new year or the week that contains January 4. We will use the formal description and we’ll use a well defined anchor date. The earliest date possible for DATETIME is January 1st 1753 which also happens to be a Monday. This date will work because 1753 is not a leap year.
We start by calculating how many days passed since January 1st 1753 by writing
Then we get an integer value of 95,170 for the date Saturday July 27th, 2013. Now, we rely on integer division. A week is 7 days so by dividing by 7 and then multiplying by 7 we get the Monday for the current week.
Then we get a value of 95,165 which is Monday, July 22, 2013. With the correct calculation for the current Monday we now want the first Thursday so we need to add three days to Monday. We write the query as
Then we have the date of Thursday July 25th, 2013.
So far, there’s nothing remarkable with the algorithm. It’s now when we use DATEPART we’ll se how clever the algorithm is. What this does is to make use of another not so known statement that say the week number is the number of passed Thursdays.”.
We now calculate the day of the year for the current Thursday like this
The last thing to do with this value is to divide by 7. But we have to deal with the fact that a number less than 7, divided by 7, we get 0 due to integer division. To avoid that we have to add 6 to the value for the calculation above and then divide by 7, like this