On the Trail of the ISO Week

,

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.

Rate

3 (3)

Share

Share

Rate

3 (3)