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
DATEPART(ISO_WEEK, '2012-01-01') yields
52, so that’s correct. But determining the ISO week-year is harder.
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 SELECT YEAR(DATEADD(DAY, (4 - DATEPART(WEEKDAY, @MyDate)), @MyDate))
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
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!
10 thoughts on “T-SQL ISO 8601 Week, Year and Start Date”
Your query is not working for the start of the 2016 year.
For @Week = 53 the result is in the 2017 year.
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
Sorry, forgotten one declaration:
DECLARE @IsoWeekStartOfYear int = DATEPART(ISO_WEEK, @FirstDayOfYear);
Hi Maxim, week 53 doesn't exist in 2016, which may explain the odd results.
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.
@Miguel, the first query with
DECLARE @MyDate datetime = '2016-01-03';yields
2015(the ISO week-year), so that is correct.
53(the ISO week).
The second query, with
DECLARE @Year int = 2015;yields "2015-12-28", which is also correct (first day of the ISO week/-year).
DECLARE @Week int = 53;
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.
The second query with a combination @Year int = 2016 and
@Week int = 1 should return "2015-12-28 00:00:00.000" !!!!
@AchMeh, please check below link for your question ans.
It's working great. Thx.
result of year 2019 with week 53 should NULL, as it does not exist.