Calculating ISO 8601 Dates in T-SQL

If you do not work with international dates much, you may never have heard of ISO 8601 and you may never need to know what I'm about to cover in this post. If so, consider yourself lucky. I am not so lucky. I have been called upon to design a few queries for international customers of Shelby Systems, and in a few cases the queries have required temporal (i.e. time-related) analysis based on the ISO 8601 standard. And although I was not lucky enough to avoid the task of working with ISO 8601 in SQL, I have learned quite a bit about temporal querying in the process, so what I have lacked in luck I have made up for in knowledge. I see that as a fair trade.

In brief, ISO 8601 is a set of rules standardizing the representation of dates and times to facilitate international commerce and communication. For an comprehensive discussion of the full ISO 8601 standard, read the article on Wikipedia. For the purposes of this post there are just three points you need to know about this standard:

• It always expresses dates and times from the largest term to the smallest, i.e. YYYY-MM-DD.
• It counts Monday as the first day of the week, not Sunday.
• It counts the first week of the year as the week containing the first Thursday of the year, not the week containing the first day of the year.

Each of these facets of the ISO 8601 standard are at odds with the default ways in which T-SQL handles dates.  There are workarounds to each one, though each workaround is more complex than the last. I'll start with the easiest first.

Express Dates and Times from the Largest Term to the Smallest

The default ways to get a datetime value and express it as a string in date notation are the following:

convert(varchar, getdate())
cast(getdate() as varchar)

Both of these will return the result in the same format. For July 7th, 2011 at 4:13 pm, it would look like this:

Jul 7 2011  4:13PM

Apart from the three-letter abbreviation for the month and lack of a comma, this is a fairly typical way of expressing the date and time in America. But it is not at all in keeping with the ISO standard. Fixing this is fairly easy, though, because the CONVERT() function has an optional format parameter, and there are values that will return true ISO-formatted date and time results. Here are the values and their outputs for the same day and time as above:

convert(varchar, getdate(), 112) = 20110707 (date only)
convert(varchar, getdate(), 108) = 16:13:24 (time only)
convert(varchar, getdate(), 126) = 2011-07-07T16:13:24.173 (complete date and time)

For the last variation, notice the T separator between date and time. Also notice the inclusion of milliseconds as a decimal fraction of a second. The last format also includes hyphens in the date where the date-only format did not. Both variations are acceptable in the ISO 8601 standard. The format without the hyphens is the "basic format," and the format with the hyphens is the "extended format."

Count Monday as the First Day of the Week

This is actually the key reason why churches may be interested in using an ISO week even if they live in the United States or other countries that do not use the ISO 8601 standard as a general rule. Most churches that have Friday or Saturday worship events want to count the following Sunday worship as part of the same weekend for statistical purposes. The ISO standard does that be lumping Sunday in with the prior six days.

However, this is uniquely tricky for the ShelbyQUERY environment because it does not allow the command SET DATEFIRST 1, which would automatically tell SQL Server to count Monday as the first day of the week. One simple workaround is to simply subtract one day from the date when doing comparisons based on which week the date belongs to. This will treat Sunday as if it were Saturday of the prior week, and it will treat Monday as if it were the first day of the week. Simply using getdate()-1 wherever you use getdate() will do that trick. Just remember not to subtract the day when you want to display the actual calendar date.

For statistical calculations based on a Monday through Sunday week, group the values by the ISO week and the ISO "week year" (i.e., the year in which the week falls -- which can be a different year than the calendar year because the first few days and last few days of the year can fall into the other year's week count). How to calculate the ISO week value is the next topic.

Count the first week of the year as the week containing the first Thursday of the year, not the week containing the first day of the year.

This is the most difficult part of ISO, and it comes down to calculating the week of the year. The DATEPART() function has a week parameter option that returns the week of the year; however, it does not follow ISO rules. It will count January 1st as being in the first week of the year no matter when it falls during the week. If you are using SQL Server 2008 or later you have it easy because Microsoft introduced a new DATEPART() parameter option in 2008 that does follow the ISO 8601 standard: iso_week. If you are using SQL Server 2005 or earlier, though, you are going to have to calculate it the hard way.

This is the algorithm I use  in the query below to calculate the ISO week of the year:

1. Let TargetDate equal the date for which we want to know the ISO week value.
2. Let CurrentThursday equal the date of the Thursday associated with the same week as the TargetDate (taking into account that Sunday belongs with the prior Thursday).
3. Let FirstThursday equal the date of the Thursday of the week containing January 4th (by definition January 4th is always in the first ISO week of the year), basing that on the year value of the CurrentThursday (not the TargetDate).
4. Let ISO_Week_Value equal the difference, in weeks, between the FirstThursday and the CurrentThursday, plus one.

Here is a query that uses variables to reflect each step of the algorithm and includes two AllInOne versions that rely on only the @TargetDate variable and no others. The FROM clause at the end is only necessary in ShelbyQUERY because of a requirement in that environment to always include a FROM clause.


declare @DateOffset as smallint, @ThursdayOffset as smallint, @TargetDate as datetime, @CurrentThursday as datetime, @FirstThursday as datetime, @ISO_WeekNumber as tinyint

/* The @DateOffset is used to make sure that Monday - Wednesday are always in the same week as the following Thursday and that Friday - Sunday are always in the same week as the prior Thursday no matter what the @@datefirst value is.
The @ThursdayOffset is used to find Thursday no matter what the @@datefirst value is. */ 

set @DateOffset = case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end
set @ThursdayOffset = case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end

set @TargetDate = getdate()


set @CurrentThursday = dateadd(day, @ThursdayOffset - datepart(dw, @TargetDate + @DateOffset), @TargetDate + @DateOffset)

set @FirstThursday = dateadd(day, @ThursdayOffset - datepart(dw, cast('1/4/' + datename(year, @CurrentThursday) as datetime) + @DateOffset), cast('1/4/' + datename(year, @CurrentThursday) as datetime) + @DateOffset)

set @ISO_WeekNumber = datediff(week, @FirstThursday, @CurrentThursday) + 1

select
 TestDate = @TargetDate,
 CurrentThursday = @CurrentThursday,
 FirstThursday = @FirstThursday,
 ISO_WeekNumber = @ISO_WeekNumber,
 ISO_WeekYear = year(@CurrentThursday),

 ISO_WeekNumber_AllInOne = datediff(week, dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, cast('1/4/' + datename(year, dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end)) as datetime) + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), cast('1/4/' + datename(year, dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end)) as datetime) + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end)) + 1,
  ISO_WeekYear_AllInOne = year(dateadd(day, case when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end))
 
from (select A = count(*) from Shelby.NANames) a