Isoweek в SQL Server 2005

В SQL Server 2008 isoweek можно найти с помощью этого:

SELECT datepart(iso_week, getdate())

Перед SQL Server 2008 не было встроенной функции для поиска isoweek.

Я искал высокий и низкий для хорошего синтаксиса, чтобы найти пользовательский iso_week для SQL Server 2005. Я нашел немало решений. Не понравилось ни одно из решений, которые я нашел, большинство из них не работало, и они были слишком длинными.

Поскольку проблема очень старая, я ожидаю, что эта проблема будет исчерпана, и найдено наилучшее решение. Однако я не смог найти хороший метод.

Я написал решение, которое я опубликую позже. Но прежде чем я это сделаю, я хочу абсолютно убедиться, что никто другой не сможет сопоставить решение, которое я написал.

Я надеюсь получить значок самообучения. Я призываю людей найти лучшие ответы на этот древний вопрос.

Я собираюсь опубликовать свой ответ, предоставив людям шанс найти хорошее решение.

Ответ 1

Здесь подход, похожий на ваш, в том, что он также полагается на эту неделю в четверг. Но в конце он использует дату по-другому.

Получить дату этой (ИСО) недели в четверг.

Ваше собственное решение использует жесткую кодировку даты известного четверга. В качестве альтернативы, эту неделю четверг можно найти с помощью @@DATEFIRST:
```
SELECT Th = DATEADD(DAY, 3 - (DATEPART(WEEKDAY, @date) + @@DATEFIRST - 2) % 7, @date)
```
(Я не слишком много боролся за правильную формулу, потому что это было уже известно).
Получить день четверга года:
```
SELECT DY = DATEPART(DAYOFYEAR, Th)
```
Используйте этот номер, чтобы узнать следующую неделю:
```
SELECT ISOWeek = (DY - 1) / 7 + 1
```

Вот приведенные выше вычисления в одном утверждении:

SELECT ISOWeek = (DATEPART(DAYOFYEAR, Th) - 1) / 7 + 1
FROM (
  SELECT Th = DATEADD(DAY, 3 - (DATEPART(WEEKDAY, @date) + @@DATEFIRST - 2) % 7, @date)
) s;

Ответ 2

Здесь есть ссылка на другие более ранние попытки http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510

Это OLD-код для функции

CREATE function f_isoweek(@date datetime)
RETURNS INT
as
BEGIN
DECLARE @rv int

SELECT @rv = datediff(ww, dateadd(ww, datediff(d, 0, dateadd(yy, datediff(yy, 0, day4),3))/7,-4),day4)
FROM (SELECT dateadd(ww, datediff(day, 0, @date)/7, 3) day4) a

RETURN @rv
END

После сочетания блестящего ответа @AndriyM с моим собственным, мы доходим до 1 строки. Это новый код.

CREATE function f_isoweek(@date datetime)
RETURNS INT
as
BEGIN

RETURN (datepart(DY, datediff(d, 0, @date) / 7 * 7 + 3)+6) / 7
-- replaced code for yet another improvement.
--RETURN (datepart(DY, dateadd(ww, datediff(d, 0, @date) / 7, 3))+6) / 7

END

Объяснение для старого кода (не будем объяснять новый код. Это фрагменты из моего кода и кода AndriyM):

Поиск буднего дня 4 выбранной даты

dateadd(week, datediff(day, 0, @date)/7, 3)

Поиск isoyear - год буднего дня 4 недели всегда в том же году, что и isoyear той недели

datediff(yy, 0, day4)

При добавлении 3 дней к первому дню isoyear обнаружен случайный день первого изовека изоия.

dateadd(yy, datediff(yy, 0, day4),3)

нахождение относительной недели первой изовеки изойя

datediff(d, 0, dateadd(yy, datediff(yy, 0, day4),3))/7

Нахождение в понедельник минус 4 дня первого изовейка приводит к четвергам недели ПЕРЕД первым днем первого изовейка изойя

dateadd(ww, datediff(d, 0, dateadd(yy, datediff(yy, 0, day4),3))/7,-4)

Знание в первый четверг недели до первой isoweek и в первый четверг недели, делает это довольно легко рассчитать неделю, не имеет значения, какая дата установки начинается с будних дней обеих дат.

datediff(ww, dateadd(ww, datediff(d, 0, dateadd(yy, datediff(yy, 0, day4),3))/7,-4),day4)

Ответ 3

Ничего себе! Очень хорошая тема и решение, чтобы избежать использования "set datefirst 1". Я просто хочу что-то добавить. Если мне нравится, вы также хотите вернуть год с неделей ISO, например "2015-01", как "Year 2015, Week 01", это может быть полезно для целей отчетности. Поскольку год с недели ИСО может отличаться от фактического года даты! Вот как я сделал это в сочетании с вашим кодом.

DECLARE @Date AS DATETIME
SET @Date = '2014-12-31'
SELECT 
       CAST(CASE WHEN MONTH(@Date) = 1 AND Q.ISOweek > 50 THEN YEAR(@Date) - 1
                 WHEN MONTH(@Date) = 12 AND Q.ISOweek < 3 THEN YEAR(@Date) + 1
                 ELSE YEAR(@Date)
            END
            AS VARCHAR(4))
    + '-'
    + RIGHT('00' + CAST(Q.ISOweek AS NVARCHAR(2)), 2) AS ISOweek
FROM (SELECT (datepart(DY, datediff(d, 0, @Date) / 7 * 7 + 3) + 6) / 7 AS ISOweek) Q

Вернется "2015-01".

Ответ 4

Мне нужно что-то подобное для PowerQuery и PowerBI и на основе ответа t-clausen.dk, я смог сделать это уравнение. Он работает так же, как и он, но использует синтаксис PowerQuery. Также базовая дата для DATEDIFF of 0 была 1/1/1900 в SQL, но в PowerQuery это 12/30/1899, поэтому я использую 2 вместо 0.

ISO Week = Number.RoundDown((Date.DayOfYear(Date.From(Duration.Days(([Date]-Date.From(2))/7)*7+5))+6)/7)

Мне также понадобился год ISO, поэтому я сделал корректировку для расчета недели ИСО и придумал:

ISO Year = Date.Year(Date.From(Duration.Days(([Date]-Date.From(2))/7)*7+3))

Измените [Date], чтобы ссылаться на столбец даты в данных.