I've been struggling through the logic of ISO 8601 myself. My solution does not look like anything I've seen in this thread yet, so I am posting my solution for consideration. The approach I took boils down to the following steps:

1. Let TargetDate equal the date for which we want to know the ISO week value.

2. Let CurrentThursday equal the date of the Thursday associated with the same week as the TargetDate (taking into account that Sunday belongs with the prior Thursday).

3. Let FirstThursday equal the date of the Thursday of the week containing January 4th, basing that on the year value of the CurrentThursday (not the TargetDate) and still taking into account that Sunday belongs with the prior Thursday. (Why January 4th? January 4th is always in the first ISO week of the year, which is not true for January 1st.)

4. Let ISO_Week_Value equal the difference, in weeks, between the FirstThursday and the CurrentThursday, plus one.

Here is a query that uses variables to reflect each step of the algorithm and includes two AllInOne versions that rely on only the @TargetDate variable and no others. The @DateOffset variable is used to make sure that Monday - Wednesday are always in the same week as the following Thursday and that Friday - Sunday are always in the same week as the prior Thursday no matter what the @@datefirst value is. The @ThursdayOffset variable is used to find Thursday no matter what the @@datefirst value is.

declare @DateOffset as smallint, @ThursdayOffset as smallint, @TargetDate as datetime,

@CurrentThursday as datetime, @FirstThursday as datetime, @ISO_WeekNumber as tinyint

set @DateOffset = case when @@datefirst > 4 then @@datefirst - 8

when @@datefirst > 1 then @@datefirst - 1 else 0 end

set @ThursdayOffset = case when @@datefirst < 5 then 5 - @@datefirst

else 12 - @@datefirst end

set @TargetDate = getdate()

set @CurrentThursday = dateadd(day, @ThursdayOffset -

datepart(dw, @TargetDate + @DateOffset), @TargetDate + @DateOffset)

set @FirstThursday = dateadd(day, @ThursdayOffset -

datepart(dw, cast('1/4/' + datename(year, @CurrentThursday) as datetime)

+ @DateOffset), cast('1/4/' + datename(year, @CurrentThursday) as datetime)

+ @DateOffset)

set @ISO_WeekNumber = datediff(week, @FirstThursday, @CurrentThursday) + 1

select

TestDate = @TargetDate,

CurrentThursday = @CurrentThursday,

FirstThursday = @FirstThursday,

ISO_WeekNumber = @ISO_WeekNumber,

ISO_WeekYear = year(@CurrentThursday),

ISO_WeekNumber_AllInOne = datediff(week, dateadd(day, case

when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end -

datepart(dw, cast('1/4/' + datename(year, dateadd(day, case

when @@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end -

datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8

when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case

when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then

@@datefirst - 1 else 0 end)) as datetime) + case when @@datefirst > 4 then

@@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end),

cast('1/4/' + datename(year, dateadd(day, case when @@datefirst < 5 then

5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate + case

when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then

@@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then

@@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end))

as datetime) + case when @@datefirst > 4 then @@datefirst - 8 when

@@datefirst > 1 then @@datefirst - 1 else 0 end), dateadd(day, case when

@@datefirst < 5 then 5 - @@datefirst else 12 - @@datefirst end -

datepart(dw, @TargetDate + case when @@datefirst > 4 then @@datefirst - 8

when @@datefirst > 1 then @@datefirst - 1 else 0 end), @TargetDate + case

when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then

@@datefirst - 1 else 0 end)) + 1,

ISO_WeekYear_AllInOne = year(dateadd(day, case when @@datefirst < 5 then

5 - @@datefirst else 12 - @@datefirst end - datepart(dw, @TargetDate +

case when @@datefirst > 4 then @@datefirst - 8 when @@datefirst > 1 then

@@datefirst - 1 else 0 end), @TargetDate + case when @@datefirst > 4 then

@@datefirst - 8 when @@datefirst > 1 then @@datefirst - 1 else 0 end))