SQLSERVER根据指定日期计算周的函数

SQLServer计算一个日期归属哪一周，如果一年中的第一周小于4天则归属倒去年的最后一周。

ALTER FUNCTION GetWeekNumber
(@date DATETIME)
RETURNS INT
AS
BEGIN
    DECLARE @WEEK INT;
    DECLARE @FirstDayOfYear DATETIME;--每年第一天
    DECLARE @DayOfWeek INT;--元旦节是周几
    SET @FirstDayOfYear=CONVERT(DATETIME,CONVERT(VARCHAR,YEAR(@date))+'-01-01');
    SELECT @DayOfWeek=CASE WHEN DATEPART(WEEKDAY, @FirstDayOfYear )-1=0 THEN 7 ELSE  DATEPART(WEEKDAY, @FirstDayOfYear )-1 END;
 
    DECLARE @CURRENTWEEK INT;
    SELECT @CURRENTWEEK=DATEPART(WEEK,CASE WHEN DATEPART(WEEKDAY, @date )-1=0 THEN DATEADD(DAY,-1,@date) ELSE @date END) ;
    
    IF @DayOfWeek>=5  AND @DayOfWeek<7
    BEGIN
        IF @CURRENTWEEK=1
        BEGIN 
            SET @WEEK=DATEPART(WEEK,DATEADD(DAY,-1,@FirstDayOfYear));
        END
        ELSE 
        BEGIN
            SET @WEEK=@CURRENTWEEK-1;
        END
    END
    ELSE 
    BEGIN 
        SET @WEEK=@CURRENTWEEK;
    END
    RETURN @WEEK;
END