获取中国周的自定义函数 GetChinaWeekNumber

报表开发，无意发现SQLServer数据库计算周跟 中国周有一点不一样，一般来讲，如果新年的1月1日开始落在的周不满4天，就需要把这几天归集到上一年的周，中国周是从周一~周日，国外的是周日~周六，所以中西方周有点不一样（网上说还有闰年不一样，我没有深入了解，先了解大概，有错误请忽喷，可以用下面的函数计算中国的周仅代表个人使用）

 

 

CREATE FUNCTION GetChinaWeekNumber
(@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