where 子句中的 dateadd - 在這種特殊情況下如何避免完全掃描？

我有以下查詢：

SELECT      u.userId,
           app.applicationId
FROM        app.application                 AS app
INNER JOIN  app.applicant                   AS ap   
           ON ap.applicantId = app.applicantId
INNER JOIN  usr.[user]                      AS u    
           ON u.userId = ap.userId
LEFT JOIN   msg.emailTemplateSent           AS t    
           ON t.toUserId = u.userId 
          AND t.emailTemplateName = 'v4_before_sixWeek_latestFlight_reminder'

WHERE       Convert(Date, GETUTCDATE())  = 
           DATEADD(week,-6,Convert(Date, app.flightDateLatest ))

AND         t.emailEventId IS NULL

ORDER BY    app.applicationId ASC

請再次注意 where 子句的第一行：

Convert(Date, GETUTCDATE())  = DATEADD(week,-6,Convert(Date, app.flightDateLatest ))

我可以用我的索引可以使用的方式來改變它嗎？索引實際上被使用，但它是一個完整的掃描。

也可以在這裡看到：

這是我的索引定義：

USE [APCore];
CREATE NONCLUSTERED INDEX i_flightDateLatest  
ON [app].[application] ( flightDateLatest  ASC  )  
INCLUDE ( [applicantId] , [applicationId] , [programID])  
WITH (  PAD_INDEX = OFF, 
       FILLFACTOR = 100  , 
       SORT_IN_TEMPDB = OFF , 
       ONLINE = OFF, 
--DROP_EXISTING = ON, 
       IGNORE_DUP_KEY = OFF, 
      STATISTICS_NORECOMPUTE = OFF, 
      DATA_COMPRESSION=PAGE, 
      ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = ON ) 
ON [NONCLUSTERED_INDEXES] 

這是完整的執行計劃。

不幸的是，我無法更改flightDateLatest從日期時間呼叫的列以避免轉換

除了創建計算列或更改架構之外，還有什麼其他方法可以避免在這種情況下讀取全表\索引掃描？

更新：

接受答案後，相應地更改查詢，現在索引用於查找操作，如下圖所示。

請注意原始查詢的 94% 成本與 6% 改進查詢的成本：

您可以將 6 週添加到您的 getutc…

然後測試該特定日期 (flightDateLatest) 是否大於或等於或DATEADD(week,6,Convert(Date, GETUTCDATE()))

小於第二天DATEADD(DAY,1,DATEADD(week,6,Convert(Date, GETUTCDATE())))

SELECT
*
FROM
(
   SELECT CAST('20220511 15:14:52.050' AS DATETIME) as flightDateLatest union all
   SELECT CAST('20220512' AS DATETIME) 
) as app
WHERE   
   app.flightDateLatest >= DATEADD(week,6,Convert(Date, GETUTCDATE()))
   AND 
   app.flightDateLatest < DATEADD(DAY,1,DATEADD(week,6,Convert(Date, GETUTCDATE())))

輸出：

flightDateLatest
2022-05-11 15:14:52.050

引用自：https://dba.stackexchange.com/questions/310332