时间戳

首页 > 解决方案 > 为什么记录没有得到进入日期的纠察队？

sql - 为什么记录没有得到进入日期的纠察队？

问题描述

我很久以前就写过这个查询，它可以工作，但现在不行了。

CREATE PROCEDURE [dbo].[WeeklyReport]
AS
BEGIN
    CREATE TABLE #temp
        (
            Area VARCHAR(20),
            NoOfInspec INT
        )

    INSERT INTO #temp
        SELECT DISTINCT
            Area, COUNT(*) AS NoOfInsp 
        FROM 
            EngineeringData E, PIRTaskList T
        WHERE
            E.EnggDataID = t.EnggDataID 
            AND T.NextInspDate BETWEEN (DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), GETDATE()))
                                   AND (DATEADD(DAY, 7 - DATEPART(WEEKDAY, GETDATE()), GETDATE()))
        GROUP BY
            Area

    SELECT
        t.Area, t.NoOfInspec AS Planned, SecTable.NoOfInsp AS Executed 
    FROM
        #temp t
    INNER JOIN
        (SELECT DISTINCT
             Area, COUNT(*) AS NoOfInsp  
         FROM
             tblScheduleHistory 
         WHERE
             EntryDate BETWEEN (DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), GETDATE()))
                           AND (DATEADD(DAY, 7 - DATEPART(WEEKDAY, GETDATE()), GETDATE()))    
        GROUP BY
            Area) SecTable ON SecTable.Area = t.Area
    ORDER BY 
        t.Area
END

几条记录的entrydate日期是

2019-10-06
2019-09-06
2019-11-06

如果有 1 个日期，则要求选择整周的记录。

标签： sqlsql-servertsqlsql-server-2012

解决方案

DATEPART(WEEKDAY, GETDATE())取决于SET DATEFIRST选项，该选项也使用SET LANGUAGE. 也可以在服务器级别配置默认语言，使用sp_configure.

例如，尝试以下操作以查看不同的结果：

DECLARE @CurrentDate DATE='2019-06-16'

SET LANGUAGE ENGLISH
SELECT  DATEPART(WEEKDAY, @CurrentDate), 
        DATEADD(DAY, 1 - DATEPART(WEEKDAY, @CurrentDate), @CurrentDate), 
        DATEADD(DAY, 7 - DATEPART(WEEKDAY, @CurrentDate), @CurrentDate)
-- returns 1, 2019-06-16, 2019-06-22

SET LANGUAGE BRITISH
SELECT  DATEPART(WEEKDAY, @CurrentDate), 
        DATEADD(DAY, 1 - DATEPART(WEEKDAY, @CurrentDate), @CurrentDate), 
        DATEADD(DAY, 7 - DATEPART(WEEKDAY, @CurrentDate), @CurrentDate)
-- returns 7, 2019-06-10, 2019-06-16

为了使代码以相同的方式工作，无论SET DATEFIRST选项如何，您都可以添加@@DATEFIRST以下方式：

DECLARE @CurrentDate DATE='2019-06-16'

SET LANGUAGE ENGLISH

SELECT  (@@DATEFIRST-1 + DATEPART(WEEKDAY, @CurrentDate))%7+1,
        DATEADD(DAY, 1 - ((@@DATEFIRST-1 + DATEPART(WEEKDAY, @CurrentDate))%7+1), @CurrentDate), 
        DATEADD(DAY, 7 - ((@@DATEFIRST-1 + DATEPART(WEEKDAY, @CurrentDate))%7+1), @CurrentDate)
-- returns 1, 2019-06-16, 2019-06-22

SET LANGUAGE BRITISH

SELECT  (@@DATEFIRST-1 + DATEPART(WEEKDAY, @CurrentDate))%7+1,
        DATEADD(DAY, 1 - ((@@DATEFIRST-1 + DATEPART(WEEKDAY, @CurrentDate))%7+1), @CurrentDate), 
        DATEADD(DAY, 7 - ((@@DATEFIRST-1 + DATEPART(WEEKDAY, @CurrentDate))%7+1), @CurrentDate)
-- returns 1, 2019-06-16, 2019-06-22

推荐阅读