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!