SQL Get "ISO Year" for ISO Week

This solution The code in the question does not return the correct value for the date '1-1-2027'.

The following will return the correct value with all dates i tested (and i tested quite a few).

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

As taken from: https://capens.net/content/sql-year-iso-week

This is the most compact solution I could come up with:

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

Can be used directly inside a SELECT statement. Or you could consider creating a user-defined function that takes the @Date parameter as input and outputs the result of the case statement.

I think this solution is much more logical and easier to comprehend for ISO-8601.

"The first week of the year is the week containing the first Thursday." see ISO Week definition

So we need to deduct the weekday of the given date from Thursday and add this to that same date to get the year.

Adding 5 and then taking the modulus of 7 to move Sunday to the previous week.

declare @TestDate date = '20270101'

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

This will result in 2026 which is correct. This will give the correct result for all dates I check between 1990-2100 using this:

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)