本文来源：https://blog.csdn.net/sqlserverdiscovery/article/details/53080695

SELECT GETDATE() AS THEDAY,
case when datepart(weekday,getdate()) in (2,3,4,5,6,7) then DATEPART(WEEK,GETDATE())
else DATEPART(WEEK,GETDATE()) -1
end AS WEEKS

考虑到周日可能是一年的第一天，所以改成这样：

select getdate() as today,
case when datepart(weekday,getdate()) in (2,3,4,5,6,7) and
DATEPART(WEEKday,datename(year,getdate())+'-01-01') = 1 then datepart(week,getdate()) +1

when datepart(weekday,getdate()) =1 and
DATEPART(WEEKday,datename(year,getdate())+'-01-01') = 1 then datepart(week,getdate())

when datepart(weekday,getdate()) in (2,3,4,5,6,7) then datepart(week,getdate())
else datepart(week,getdate()) -1
end as week