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"

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

Answer 1

这是我能想到的最紧凑的解决方案：

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语句的结果。

Answer 2

我认为对于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)

Answer 3

我认为Bart Vanseer的解决方案很好，很简单，但我认为应该是1。

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;