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.