Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

On the Trail of the ISO Week

By Christoffer Hedgate,

Or the case of a case of a case of...

The other day, at a Swedish discussion forum that I visit, someone asked why SQL Server regards week 53 as week 1, and how to get around it. My first -- and simple -- answer was of course that this is simply the way they have implemented the week number function (DATEPART(wk, @date)) in SQL Server. In Sweden -- as well as many other countries -- we use the international standard ISO 8601 for defining week numbers, but this is not the case in America. It would of course have been nice of the SQL Server team to supply a function where we could choose which way we want to calculate the week number (as in Oracle and many other RDBMSs that supply both a proprietary implementation as well as others such as ISO 8601), but since they did not we will have to make our own implementation.

A quick Google search gave me no other results than some implementations of a stored procedure and/or user-defined function, and actually these were not always complete since some of them depended on @@datefirst being set to 1 (the global variable @@datefirst returns the current setting for which day of the week is considered as the starting day of the week, where 1=Monday, 2=Tuesday and so on). Anyway, as we all know, a problem that is not solvable in a set-based T-SQL statement is not a problem for the database to solve. And furthermore it is of course always more fun to do stuff yourself. If it is not invented here... :-)

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, which is why some of the implementations I found depended on @@datefirst being set to 1. To make it perfectly clear, note that week 1 of a given year can actually start on a date of the previous year, and the first days of a year might actually be part of week 52 (or 53) of the previous year.

Algorithm for calculating the ISO week number
Now, the algorithm to calculate the ISO week number for a given date is not very difficult (see this description and a C# implementation of it). We start by calculating the day of the week for the fourth of January of the year that our particular date is part of. Since we know that 4-Jan is always part of week 1 of the year, we can now easily find out which date that week 1 of the year started with. If 4-Jan is a Monday then the starting date of week 1 is of course 4-Jan. If it happens to be a Tuesday then it is one day earlier, a Wednesday then two days earlier, and so on until when 4-Jan is a Sunday and we know that week 1 started six days earlier (which is 29-Dec of the previous year). So, with that info at hand we can now calculate the ISO week number for a given date by subtracting the start date of week 1 from the given date, dividing the resulting number of days by 7 and adding 1 to this number.

But (always the but), there are a couple of exceptions to this. As said earlier, 29-Dec, 30-Dec and 31-Dec might actually be part of week 1 of the next year, and 1-Jan, 2-Jan and 3-Jan sometimes belongs to the last week of the previous year. So, these dates become exceptions to the algorithm and we will calculate them specifically in our T-SQL statement. If the start date of week 1 (as calculated above) is later than the tested date in the range 1-Jan through 3-Jan, then we know the date is part of the final week of the previous year. Calculate start date of week 1 of the previous year, subtract from our tested date, divide by 7 and add 1 to get the ISO week number (52 or 53, of the previous year). For the dates 29-Dec through 31-Dec we do it the other way round. If the tested date is greater than or equal to the start date of week 1 of the following year, then we know that the date is part of that week.

ISO week number in T-SQL
So, that is the background regarding week numbers according to ISO 8601 and how to calculate it for a given date. Now here is the T-SQL statement to calculate it for a <date expression> (e.g. a column, variable etc):

SELECT <date expression>, CASE

 -- Exception where <date expression> is part of week 52 (or 53) of the previous year
 WHEN <date expression> < CASE (DATEPART(dw, CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
  WHEN 1 THEN CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04'
  WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04')
  WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04')
  WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04')
  WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04')
  WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04')
  ELSE DATEADD(d, -6, CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04')
  END
 THEN
  (DATEDIFF(d,
   CASE (DATEPART(dw, CAST(YEAR(<date expression>) - 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
   WHEN 1 THEN CAST(YEAR(<date expression>) - 1 AS CHAR(4)) + '-01-04'
   WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(<date expression>) - 1 AS CHAR(4)) + '-01-04')
   WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(<date expression>) - 1 AS CHAR(4)) + '-01-04')
   WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(<date expression>) - 1 AS CHAR(4)) + '-01-04')
   WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(<date expression>) - 1 AS CHAR(4)) + '-01-04')
   WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(<date expression>) - 1 AS CHAR(4)) + '-01-04')
   ELSE DATEADD(d, -6, CAST(YEAR(<date expression>) - 1 AS CHAR(4)) + '-01-04')
   END,
   <date expression>
  ) / 7) + 1

 -- Exception where <date expression> is part of week 1 of the following year
 WHEN <date expression> >= CASE (DATEPART(dw, CAST(YEAR(<date expression>) + 1 AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
  WHEN 1 THEN CAST(YEAR(<date expression>) + 1 AS CHAR(4)) + '-01-04'
  WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(<date expression>) + 1 AS CHAR(4)) + '-01-04')
  WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(<date expression>) + 1 AS CHAR(4)) + '-01-04')
  WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(<date expression>) + 1 AS CHAR(4)) + '-01-04')
  WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(<date expression>) + 1 AS CHAR(4)) + '-01-04')
  WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(<date expression>) + 1 AS CHAR(4)) + '-01-04')
  ELSE DATEADD(d, -6, CAST(YEAR(<date expression>) + 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(<date expression>) AS CHAR(4)) + '-01-04') + @@DATEFIRST - 1) % 7
  WHEN 1 THEN CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04'
  WHEN 2 THEN DATEADD(d, -1, CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04')
  WHEN 3 THEN DATEADD(d, -2, CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04')
  WHEN 4 THEN DATEADD(d, -3, CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04')
  WHEN 5 THEN DATEADD(d, -4, CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04')
  WHEN 6 THEN DATEADD(d, -5, CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04')
  ELSE DATEADD(d, -6, CAST(YEAR(<date expression>) AS CHAR(4)) + '-01-04')
  END,
  <date expression>
  ) / 7) + 1
 END AS IsoWeek

I guess that at first glance the above statement looks kind of complicated. Sure, I know, most of you read it without problem, but for those that do not here is a breakdown of how it works.

Ignoring the @@datefirst setting
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, <date expression>) 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.

Day of the week for 4-Jan
The inner CASE statements that are used a couple of times all return the starting date of week 1, for the current, previous or following year (as necessary depending on where they are used). This is done by using the year part of <date expression> with '-01-04' to create a 4-Jan date, then checking which day of the week (ignoring @@datefirst setting as specified above) that date is and subtracting the appropriate number of days from 4-Jan, returning the date for Monday of week 1.

Applying the algorithm
Now it is simply a matter of applying the algorithm described above to the T-SQL statement. First we check for the two exceptions. If the <date expression> being checked is less than the week 1 starting date of the same year we calculate the ISO week number for the previous year, resulting in 52 or 53. If the <date expression> being checked is greater than or equal to the starting date of next year's week 1 then we know that it is part of week 1 for the next year. Otherwise we just calculate the ISO week number as the difference in days between the starting date of week 1 and the <date expression> being checked, divide this by 7 and add 1 to return the week number.

A final note
This T-SQL statement simply returns the ISO week number for a specific date, without any regard to which year it belongs to. If this is necessary for the functionality you are implementing then you will need to add that part to the statement. Simply add YEAR(<date expression>)*100 to the ISO week number. For the exceptions you must also add or subtract (depending on which exception) 1 to the year of <date expression> before multiplying it with 100. This way the statement will return the week number as 200452. By dividing this with 100 you get the year that the week belongs to and the remainder is the ISO week number.

Total article views: 10814 | Views in the last 30 days: 7
 
Related Articles
FORUM

Week numbers and week start dates

Week numbers and week start dates

FORUM

Date in Expression Builder

Using DateAdd

FORUM

Cannot start MS SQL MANAGEMENT STUDIO EXPRESS

Cannot start MS SQL MANAGEMENT STUDIO EXPRESS

FORUM

Having problem starting SQL Server Express

Having problem starting SQL Server Express

ARTICLE

Date Manipulation with DATEADD/DATEDIFF

Learn how to use Dateadd/Datediff functions to manipulate dates in this short article from Seth Phel...

Tags
advanced querying    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones