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 
/*
today week
2017-03-15 10:55:53.507

*/

--自定义函数GetWeek（）来实现

CREATE FUNCTION [dbo].[GetWeek](@theday DATETIME)
RETURNS INT
AS
BEGIN
DECLARE @theweek INT
select  @theweek = case when datepart(weekday,@theday) in (2,3,4,5,6,7) and DATEPART(WEEKday,datename(year,@theday)+'-01-01') = 1
then datepart(week,@theday) +1
                  when datepart(weekday,@theday) =1  and DATEPART(WEEKday,datename(year,@theday)+'-01-01') = 1
then datepart(week,@theday)  
when datepart(weekday,@theday) in (2,3,4,5,6,7)
then datepart(week,@theday) 
else datepart(week,@theday)  -1 END
RETURN @theweek
END

————————————————

版权声明：本文为CSDN博主「不想长大啊」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/yupeigu/java/article/details/53080695