报表开发,无意发现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
标签:DayOfWeek,FirstDayOfYear,DATEPART,函数,自定义,INT,GetChinaWeekNumber,date,DECLARE From: https://www.cnblogs.com/shexunyu/p/18101712