T-SQL ISO 8601 Week, Year and Start Date

The ISO 8601 week dates standard seems very simple, but is hard to use in (T-)SQL. Let’s start with a couple of example dates:

  • 2012-01-01 (Sunday) is in 2011-W52, first date (Monday) is 2011-12-26
  • 2013-01-01 (Tuesday) is in 2013-W01, first date (Monday) is 2012-12-31
  • 2016-01-01 (Friday) is in 2015-W53, first date (Monday) is 2015-12-28

The easy part is getting the ISO week number from a datetime. Executing DATEPART(ISO_WEEK, '2012-01-01') yields 52, so that’s correct. But determining the ISO week-year is harder. YEAR('2012-01-01') yields 2012 which is correct as a date, but not for the ISO week-year (which is 2011, see example above).

The following determines the ISO week-year. First, Monday is set as the first day of the week. Than, the difference between Thursday (day number 4) and the given day of the week is added to the given date. The year of the calculated date (which is the Thursday in the week of the given date) is the ISO week-year.

-- Input
DECLARE @MyDate datetime = '2012-01-01';

-- Configuration
SET DATEFIRST 1; -- Monday is first day of the week

-- Execute

The query above returns 2011, which is correct.

The following determines the first date of a given ISO week. When the first day of the given year is Monday, Tuesday, Wednesday or Thursday, it is in ISO week 1 of the given year. Otherwise, it is in the last ISO week of the previous year. Next, we determine the date of the Monday in the week in which the first day of the given year falls. Finally, we add the number of given ISO weeks to that date, corrected with whether the first day of the year is in week 1 or not.

-- Input
DECLARE @Year int = 2013;
DECLARE @Week int = 1;
-- Config
SET DATEFIRST 1; -- Monday is first day of the week
-- Calculate
DECLARE @FirstDayOfYear datetime = DATEFROMPARTS(@Year, 1, 1);
DECLARE @IsFirstDayInFirstWeek int = CASE WHEN (DATEPART(WEEKDAY, @FirstDayOfYear) <= 4) THEN 1 ELSE 0 END;
DECLARE @StartOfWeek datetime = DATEADD(WEEK, @Week - @IsFirstDayInFirstWeek, DATEADD(DAY, (1 - DATEPART(WEEKDAY, @FirstDayOfYear)), @FirstDayOfYear));

-- Execute
SELECT @StartOfWeek

The query above returns 2012-12-31, which is correct.

Take note that DATEFROMPARTS works from SQL Server version 2012, but that it can easily be replaced using string concatenation to implicitly create a datetime. Furthermore, SET DATEFIRST 1; has session-scope, so any date calculations during the SQL session may be affected!

Try the queries with the examples to see that they work. Hope this helps!

6 thoughts on “T-SQL ISO 8601 Week, Year and Start Date”

  1. Your query is not working for the start of the 2016 year.
    For @Week = 53 the result is in the 2017 year.

  2. Here is the additional code which should used at the beginning of "Calculation" block:
    DECLARE @WeekStartOfYear INT = DATEPART(WEEK, @FirstDayOfYear);

    IF(@IsoWeekStartOfYear > @WeekStartOfYear AND @Week = @IsoWeekStartOfYear)
    SET @Week = 0

  3. Sorry, forgotten one declaration:
    DECLARE @IsoWeekStartOfYear int = DATEPART(ISO_WEEK, @FirstDayOfYear);

  4. Hi Thijs. The point that Maxim is trying to explain is that Friday 01, Saturday 02 and Sunday 03 (2016) are in week 53 (2015) according to ISO calendar. We have to consider it.

  5. @Miguel, the first query with DECLARE @MyDate datetime = '2016-01-03'; yields 2015 (the ISO week-year), so that is correct.

    DATEPART(ISO_WEEK, '2016-01-03') yields 53 (the ISO week).

    The second query, with DECLARE @Year int = 2015;
    DECLARE @Week int = 53;
    yields "2015-12-28", which is also correct (first day of the ISO week/-year).

    I fail to understand what part of which query doesn't work as expected. When you execute the second query with a combination of ISO week and year that do not exist (like 53, 2016), the result will not be defined.

Leave a Reply

Your email address will not be published. Required fields are marked *