sqlserver中获取本周记录

sqlserver中获取本周记录，由于西方以周日作为本周的第一天，中国传统以周一作为本周的第一天，所以在查询的时候需要做特殊处理

首先：创建一个标量函数

CREATE FUNCTION [dbo].[GETWEEKNUMBER](@DATE DATETIME)  
RETURNS INTEGER
AS
BEGIN 

    DECLARE @FIRST_DATE_OF_YEAR DATETIME = DATEADD(YYYY,DATEDIFF(YYYY,0,@DATE),0) 
    -- DECLARE @MONDAY_OF_WEEK DATETIME = DATEADD(WK,DATEDIFF(WK,0,@DATE),0) 
    -- DECLARE @PREVIOUS_DATE DATETIME = DATEADD(DAY,-1,@DATE)
    DECLARE @WEEK_NUMBER INTEGER

    -- 如果当前时间是当前年的第一天
    IF @DATE = @FIRST_DATE_OF_YEAR
        SET @WEEK_NUMBER = 1 
    -- 星期天是年第一天的情况
    ELSE IF (DATEPART(WEEKDAY,@DATE) = 1 AND DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,@DATE)/7 + 1 = DATEPART(WEEK,@DATE))  
        SET @WEEK_NUMBER = DATEPART(WEEK,@DATE)  
    -- 星期天不是年第一天的情况
    ELSE IF (DATEPART(WEEKDAY,@DATE) = 1 AND DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,@DATE)/7 + 1 <> DATEPART(WEEK,@DATE)) 
        SET @WEEK_NUMBER = DATEPART(WEEK,@DATE) - 1 
    -- 如果当前天的上一个周日小于年第一天
    ELSE IF DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0)) < @FIRST_DATE_OF_YEAR 
        SET @WEEK_NUMBER = 1 
    -- 当前天前面的一个周日正好是以周日为开始年的 7 倍的天数
    ELSE IF DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0) ))/7 + 1 = DATEPART(WEEK,@DATE) 
        SET @WEEK_NUMBER = DATEPART(WEEK,@DATE) + 1  
    ELSE 
        SET @WEEK_NUMBER = DATEPART(WEEK,@DATE)   
           
    RETURN @WEEK_NUMBER
END

GO

　　2，接着调用即可

select Convert(varchar(100),a.STATISTICSDATE,23) as STATISTICSDATE,SUM(a.LATENUM) as LATENUM ,SUM(a.BACKNUM) as BACKNUM ,SUM(a.ABSENTNUM) as ABSENTNUM ,SUM(a.UNCOMENUM) as UNCOMENUM from dbo.BASE_ATTENDANCE a where dbo.GETWEEKNUMBER(GETDATE())=dbo.GETWEEKNUMBER(a.STATISTICSDATE)
group by Convert(varchar(100),a.STATISTICSDATE,23)

这样查询出来的就是本周的数据，并且是以周一作为本周第一天

 

转载于:https://www.cnblogs.com/sharestone/p/10373048.html