GETDATE功能报告

Question

我将如何修改这个以检索昨天和6天前？

现在，这种情况只在昨天才回来。

AND (DATEDIFF(d,[dbo].[UDO_FUNC_GET_PANEL_TIME](EV2.EVENT_TIME_UTC,EV2.MACHINE),GETDATE()) = 1)

当我将1改为2时，它会在前一天返回。

AND (DATEDIFF(d,[dbo].[UDO_FUNC_GET_PANEL_TIME](EV2.EVENT_TIME_UTC,EV2.MACHINE),GETDATE()) = 2)

我需要它回来7天。

如果需要，下面是完整的查询：

CREATE VIEW [dbo].[UDO_VW_ExportView]
AS
SELECT TOP 100 PERCENT '"'+CONVERT( NVARCHAR(20),dbo.UDO_FUNC_GET_PANEL_TIME(EV.EVENT_TIME_UTC,EV.MACHINE),101)+' '+SUBSTRING(CONVERT(NVARCHAR(20),dbo.UDO_FUNC_GET_PANEL_TIME(EV.EVENT_TIME_UTC,EV.MACHINE),100),13,7)+'"' AS Q_EventDate_AMPM
                      ,'"'+CONVERT( NVARCHAR(20),GETDATE(),101)+' '+SUBSTRING(CONVERT(NVARCHAR(20),GETDATE(),100),13,7)+'"' AS Q_currentDate_AMPM
                      ,'"'+E.SSNO+'"' AS Q_CardholderId
                      ,'"'+CAST(EV.CARDNUM AS NVARCHAR(20))+'"' AS Q_BadgeId
                      ,'"'+E.LASTNAME+'"' AS Q_LastName
                      ,'"'+E.FIRSTNAME+'"' AS Q_FirstName
                      ,'"'+A.NAME+'"' AS Q_PanelName
                      ,'"'+R.READERDESC+'"' AS Q_ReaderName
FROM dbo.EMP E
     INNER JOIN dbo.EVENTS EV ON EV.EMPID = E.ID
                                 AND EV.EVENT_TIME_UTC =
(
    SELECT MIN(EV2.EVENT_TIME_UTC)
    FROM EVENTS EV2
    WHERE EV2.EMPID = E.ID
          AND EV2.EVENT_TIME_UTC > GETUTCDATE() - 3
          AND (DATEDIFF(d,[dbo].[UDO_FUNC_GET_PANEL_TIME](EV2.EVENT_TIME_UTC,EV2.MACHINE),GETDATE()) = 1)
)
     INNER JOIN dbo.ACCESSPANE A ON A.PANELID = EV.MACHINE
     INNER JOIN dbo.READER R ON R.PANELID = EV.MACHINE AND R.READERID = EV.DEVID
ORDER BY EV.EMPID, EV.EVENT_TIME_UTC
GO

Answer 1

不要使用= 1或= 2，而是使用< 7