SQL 获取 ISO 周的 "ISO 年份"

Question

我需要计算一周被分配到的年份。例如，2003年12月29日（星期一）被分配到了2004年的第一周（这只适用于欧洲，我想）。您可以使用以下代码查看：

SELECT DATEPART(isowk, '20141229');

但是现在我需要一种简单的方法来获取本周分配的年份。我目前的做法并不那么优雅：

DECLARE @week int, @year int, @date char(8)

--set @date = '20150101'
set @date = '20141229'


SET @week = cast(datepart(isowk, @date) as int)

if @week = 1
begin
      if DATEPART(MONTH, @date) = 12
      begin
            set @year = DATEPART(year, @date) + 1
      end
      else
      begin
            set @year = DATEPART(year, @date)
      end
end

select @date "DATE", @week "WEEK", @year "YEAR"

如果有人知道更优雅的方式，那就太好了 :-)

- Romano Zumbé

---

不要忘记添加你特定的关系型数据库标签，以吸引正确的人回答你的问题。 - peter.hrasko.sk

你还需要处理第52周跨越到1月份的情况（在这种情况下，你的周年份比当前年份少1）。请参见我的下面的答案。 - Dan

Answer 1

这个解决方案问题中的代码不能正确返回日期'1-1-2027'的值。

以下代码将返回我测试过的所有日期的正确值（而且我测试了相当多）。

SELECT YEAR(DATEADD(day, 26 - DATEPART(isoww, '2012-01-01'), '2012-01-01'))

来源： https://capens.net/content/sql-year-iso-week

- nilla

---

Nilla nilla请不要使用第二个账户编辑帖子。 - Amin Negm-Awad

我相信这个答案对于1-1-2027确实是有效的。结果给出了2026，这是正确的，因为1-1-2017是星期五，这意味着ISO周将是2026年的最后一周。 - Josh Schultz

“26”让我感到很头痛。我本能地了解正在发生的事情，但我想知道这里适用的值范围。为什么是26？是的，我可以确认对于所有我的数据，这个方法都可以奏效。 - Simon_Weaver

真是一种非常优雅的解决方案。通过将日期向前移动25天以获取来年的年份，或者减去26或27天来推迟日期以提取正确的年份，从而将日期推入正确的年份以提取日历年份。 - Knut Boehnert

@BeChillerToo，2016年1月1日是星期五，而不是星期四（至少在Windows 11的日历中是这样），因此2016年1月1日实际上是2015年最后一个ISO周的一部分。 - derpirscher

Answer 2

这是我能想到的最简洁的解决方案：

CASE
    WHEN DATEPART(ISO_WEEK, @Date) > 50 AND MONTH(@Date) = 1 THEN YEAR(@Date) - 1
    WHEN DATEPART(ISO_WEEK, @Date) = 1 AND MONTH(@Date) = 12 THEN YEAR(@Date) + 1
    ELSE YEAR(@Date) END

可以直接在SELECT语句中使用。或者你可以考虑创建一个用户定义的函数，该函数以@Date参数作为输入，并输出case语句的结果。

- Dan

---

可以通过将 YEAR(@Date) 位移到 CASE 表达式外部来稍微缩短一下代码：CASE WHEN ... THEN -1 WHEN ... THEN +1 ELSE 0 END + YEAR(@Date)。 - Andriy M

这似乎是有效的。 - Be Chiller Too

Answer 3

我认为这个解决方案对于ISO-8601来说更加合理和易于理解。 “一年的第一周是包含第一个星期四的那一周。” 参见ISO周定义。 因此，我们需要从给定日期减去星期几直到星期四，然后把它加上同一日期以获取该年份。 加5再对7取模可将星期日移到前一周。

declare @TestDate date = '20270101'

select year(dateadd(day, 3 - (datepart(weekday, @TestDate) + 5) % 7, @TestDate))

这将得出2026年的正确结果。使用以下内容，可以为1990-2100年间我检查的所有日期提供正确结果：

declare @TestDate date = '19900101'
declare @Results as table
(
    TestDate    date,
    FirstDayofYear  varchar(20),
    ISOYear int,
    ISOWeek int
)

while (@TestDate < '21000201')
begin
    insert @Results(TestDate, FirstDayofYear, ISOYEar, ISOWeek)
    select @TestDate, datename(weekday, dateadd(day, datepart(day, @TestDate) * -1 +1, @TestDate)),
        year(dateadd(day, 3 - (datepart(weekday, @TestDate) + 5) % 7, @TestDate)), datepart(ISOWK, @TestDate)
    
    set @TestDate = dateadd(day, 1, @Testdate)

    if(datepart(day, @TestDate) > 7)
    begin
        set @TestDate = dateadd(year, 1, dateadd(day, datepart(day, @TestDate) * -1 + 1, @TestDate))
    end
end

-- Show all results that are wrong:
select * from @Results 
where (ISOYear <> datepart(year, TestDate) and ISOWeek < 3)
or (ISOYear = datepart(year, TestDate) and ISOWeek >= 52)

- Bart Vanseer

---

不适用于“2015-01-04”（星期日），您的代码返回“2014”，但2015-01-04应为“2015-W01-7”。 - Be Chiller Too

我发现并喜欢这个解决方案，它帮助了我，谢谢。我注意到可以使用以下方法创建一个ISO星期几函数：datepart(weekday, dateadd(day, -1, @TestDate)。然后是：year(dateadd(day, 4 - IsoWeekDay(@TestDate), @TestDate)来获取年份。 - Kipperfer

Answer 4

我认为Bart Vanseer的解决方案很好，而且简单易懂，但是有偏差。 我相信应该是这样：

select year(dateadd(day, 3 - ((datepart(weekday, @TestDate) + 5) % 7) , @TestDate))

尝试以下步骤，找出它们之间的几个不同之处。

    declare @TestDate date = '2000-01-01' 

    DECLARE @cnt INT = 0;
    DECLARE @cnt_total INT = 10000;

    WHILE @cnt < @cnt_total
    BEGIN
       SET @TestDate = dateadd(day,1, @TestDate)

       if year(dateadd(day, 3 - (datepart(weekday, @TestDate) + 6) % 8, @TestDate)) <>
          year(dateadd(day, 3 - ((datepart(weekday, @TestDate) + 5) % 7) , @TestDate))
       BEGIN
         select @TestDate, year(dateadd(day, 3 - (datepart(weekday, @TestDate) + 6) % 8, @TestDate))
         select @TestDate, year(dateadd(day, 3 - ((datepart(weekday, @TestDate) + 5) % 7) , @TestDate))
       END

       SET @cnt = @cnt + 1;
    END;

- user2663901

---

我刚刚检查了一下，这似乎适用于2015年到2023年，我认为这应该是被接受的答案。 - Be Chiller Too

的确，我的偏差是1。我运行了一个1990-2100年的测试脚本，这纠正了我的错误。 - Bart Vanseer

Answer 5

受其他答案的启发，我得到了以下解决方案：

declare @TestDate date = '20270101'

SELECT DATEPART(year, DATEADD(day, 4 - DATEPART(weekday, @TestDate), @TestDate))

只需获取本周的星期四即可得到正确的年份。

假设 DATEFIRST 设置为星期一（1）。

- smonkey

---

那不行。它会给你星期四所在的年份，但是这个星期可能仍然属于上一年。编辑：啊，你说得对。那个星期四应该是标志着第一周的。已经有几年了。但我觉得你的解决方案有些问题，可能需要再思考一下。 - Romano Zumbé

因为ISO周定义规定：“一年中的第一周是包含第一个星期四的那个星期”，所以星期四永远会在正确的年份上。 - smonkey

只有七种可能的情况：一年的第一天是星期一、星期二、星期三、星期四、星期五、星期六或星期日。星期一到星期四的情况下，这周属于新年，包括星期四。星期五到星期日的情况下，这周属于旧年，包括星期四。 - smonkey

有趣的是，这是唯一一个提到datefirst的答案，无论是以任何形式。那个设置真的很重要吗？毕竟，ISO意味着国际标准，因此你所在国家的本地化不应该有影响。这是我的直觉，但我只是想知道正确的答案。 - undefined

应该考虑DATEFIRST的原因是因为DATEPART的weekday取决于DATEFIRST。 - undefined

如果你的DATEFIRST是7（星期日），你需要从5（而不是4）减去来确定当前周的星期四。 所以上述查询只适用于DATEFIRST等于1的情况，但可以轻松地适应其他DATEFIRST。也可以考虑DATEFIRST，但会增加复杂性。 - undefined

Answer 6

使用datetrunc函数获取iso_week的起始日期，再加上3天，取年份。

iso_week的定义基于以下规则：

引用： 第一周是包含第一个星期四的那一周。

使用datetrunc函数（SQL Server 2022）可以更清晰地表达这一点。

DATEPART(year, DATEADD(day, 3, DATETRUNC(iso_week, <date_column> )))

找到一周开始的日期（星期一），再加上3天（到达星期四），然后取年份。

---

例子：

所以基于2020年01月01日（星期三）的日期

DATETRUNC(iso_week, '2020-1-1')    

返回ISO周的第一天的实际日期。

2019-12-30 00:00:00.0000000

然后DATEPART(year, '2019-12-30')给我们2019。

---

我实际上可以记住这个方法！

而且，考虑到这只是一个标量计算，与其他方法相比似乎并没有太大的区别。

- Simon_Weaver

Answer 7

DECLARE @date DATETIME
SET @date='2014-12-29'
SELECT 
CASE --Covers logic for ISO week date system which is part of the ISO 8601 date and time standard.  Ref: https://en.wikipedia.org/wiki/ISO_week_date
  WHEN (DATEPART(ISO_WEEK,@date) = 53) AND (DATEPART(MONTH,@date) = 1)
    THEN CAST((DATEPART(YEAR, @date) - 1) AS varchar(4)) + ('-W') + CAST (RIGHT('0' + CAST(DATEPART(ISO_WEEK,@date) AS varchar(2)),2) AS varchar(2))
  WHEN (DATEPART(ISO_WEEK,@date) = 1) AND (DATEPART(MONTH,@date) = 12)
    THEN CAST((DATEPART(YEAR,@date) + 1) AS varchar(4)) + ('-W') + CAST (RIGHT('0' + CAST(DATEPART(ISO_WEEK,@date) AS varchar(2)),2) AS varchar(2))
  ELSE CAST(DATEPART(YEAR,@date) AS varchar(4)) + ('-W') + CAST (RIGHT('0' + CAST(DATEPART(ISO_WEEK,@date) AS varchar(2)),2) AS varchar(2))
 END AS ISO_week

- gt0524b

Answer 8

对于IsoYear，获取IsoWeek中的星期四，然后获取年份。

- Miguel Zavala