Как получить первый день недели, когда я знаю номер недели в ISO 8601

Для любой даты этот SQL вернет ISO-неделю: SELECT DATEPART(ISO_WEEK, @date). Ниже приведен уродливый (но довольно эффективный) однострочник для расчета первой даты этой ISO-недели:

DECLARE @date DATETIME = '2012-09-16'
SELECT (
    CASE DATEPART(ISO_WEEK, @date) 
        WHEN DATEPART(ISO_WEEK, DATEADD(DAY,-6,@date)) THEN DATEADD(DAY,-6,@date)
        WHEN DATEPART(ISO_WEEK, DATEADD(DAY,-5,@date)) THEN DATEADD(DAY,-5,@date)
        WHEN DATEPART(ISO_WEEK, DATEADD(DAY,-4,@date)) THEN DATEADD(DAY,-4,@date)
        WHEN DATEPART(ISO_WEEK, DATEADD(DAY,-3,@date)) THEN DATEADD(DAY,-3,@date)
        WHEN DATEPART(ISO_WEEK, DATEADD(DAY,-2,@date)) THEN DATEADD(DAY,-2,@date)
        WHEN DATEPART(ISO_WEEK, DATEADD(DAY,-1,@date)) THEN DATEADD(DAY,-1,@date)
        ELSE DATEADD(DAY,0,@date)
    END
) FirstDayOfISOWeek

Теперь это не самая красивая моя работа, но она работает методом проб и ошибок - проверка того, сколько дней назад неделя ISO осталась прежней, а затем возвращение к этому дню.

Я думаю, что лучшее, что можно сделать с бизнес-данными, — это хранить их в таблицах. А недели ISO — это бизнес-данные. Если бы у вас была таблица, которая выглядела бы так

calendar_date   iso_year    iso_week
--
2016-01-01      2015        53
2016-01-02      2015        53
2016-01-03      2015        53
2016-01-04      2016        1
2016-01-05      2016        1
2016-01-06      2016        1
2016-01-07      2016        1

затем отвечая на вопрос «Какова первая дата первой недели ISO в 2016 году?» упрощается до этого.

select min(calendar_date) 
from calendar
where iso_year = 2016;
  and iso_week = 1;

Вот как создать такую ​​таблицу. (Тщательно не проверено.)

create table calendar (
  calendar_date date primary key,
  iso_year integer not null,
  iso_week integer not null,
  check (iso_week = datepart(iso_week, calendar_date)),
  check (iso_year = year(dateadd(wk, datediff(d, 0, calendar_date) / 7, 3)))
);

Этот код заполнит его.

declare @start_date date;
select @start_date = '2016-01-01';
declare @end_date date;
select @end_date = '2050-12-31';

declare @next_date date;
select @next_date = (select coalesce(max(calendar_date), @start_date) from calendar);

while @next_date <= @end_date 
begin
    begin transaction;
    insert into calendar values (@next_date , year(dateadd(wk, datediff(d, 0, @next_date) / 7, 3)), datepart(iso_week, @next_date));
    commit;
    select @next_date = dateadd(d, 1, @next_date);
end

Тщательно контролируйте привилегии. Очень немногие люди должны иметь права на вставку, обновление или удаление.

Функция CLR также возможна

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static DateTime fnFirstDateOfWeekISO8601(int year, int weekOfYear)
    {
        DateTime jan1 = new DateTime(year, 1, 1);
        int daysOffset = DayOfWeek.Thursday - jan1.DayOfWeek;

        DateTime firstThursday = jan1.AddDays(daysOffset);
        var cal = CultureInfo.CurrentCulture.Calendar;
        int firstWeek = cal.GetWeekOfYear(firstThursday, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);

        var weekNum = weekOfYear;
        if (firstWeek <= 1)
        {
            weekNum -= 1;
        }
        var result = firstThursday.AddDays(weekNum * 7);
        return result.AddDays(-3);
    }
}

#обновление 2020-09-13

У Microsoft уже есть класс для этого https://docs.microsoft.com/en-us/dotnet/api/system.globalization.isoweek?view=netcore-3.1 поэтому функция CLR может выглядеть более простой

using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    using System.Globalization;
    
    public partial class UserDefinedFunctions
    {
        [Microsoft.SqlServer.Server.SqlFunction]
        public static DateTime fnFirstDateOfWeekISO8601(int year, int weekOfYear)
        {
           return System.Globalization.ISOWeek.ToDateTime(year, weekOfYear, DayOfWeek.Monday); 
        }
    }