They are not equivalent. Records that are 7 days full days ago but are before the current time of day will only be returned in query #2:
When comparing days using the
DATEADD function, it does not take the time part into consideration. The function will 1 if the you are comparing Sunday & Monday regardless of the times.
DECLARE @MyTable TABLE(pk INT, LogInsertTime DATETIME);
VALUES (1, DATEADD(HOUR, 1, CAST(DATEADD(DAY, -7, CAST (GETDATE() AS DATE))AS DATETIME))),
(2, DATEADD(HOUR, 23, CAST(DATEADD(DAY, -7, CAST (GETDATE() AS DATE)) AS DATETIME)));
DECLARE @DateTime DATETIME = GETDATE();
WHERE DATEDIFF(DAY, LogInsertTime, @DateTime) > 7;
-- 0 records.
WHERE LogInsertTime < @DateTime - 7;
-- 1 record.
The logical equivalent of the first query that will enable potential index usage is to either remove the time part of
@DateTime or to set the time to
WHERE LogInsertTime < CAST(@DateTime - 7 AS DATE);
The reason why the first query cannot use a index on
LogInsertTime is because the column is buried within a function. Query #2 compares the column to a constant value which enables the optimizer to choose a index on