Are these two queries logically equivalent?

Question

Are these two queries logically equivalent?

DECLARE @DateTime DATETIME = GETDATE()

Query 1

SELECT *
FROM   MyTable
WHERE  Datediff(DAY, LogInsertTime, @DateTime) > 7   

Query 2

SELECT *
FROM   MyTable
WHERE  LogInsertTime < @DateTime - 7 

If they are not logically equivalent, can you give me the logical equivalent of the first query so that the WHERE clause can effectively use an index (i.e. eliminate function wrapping)?

Answer 1

Whether the two queries you posted are logically equivalent is irrelevant; you shouldn't use either of them. I will try to steer you away from a couple of things:

1. Whenever possible, try to avoid applying functions to columns. It's always as good, and mostly better, to keep those calculations against constants and not columns - this can destroy SARGability and render indexes on those columns useless. In this case, I much prefer query 2, especially if LogDateTime is indexed (or might ever be).

2. I don't like the shorthand date math and I recommend against it. Sure, it's faster to type, but try that with a DATE data type and you will get an ugly error. Much better to spell it out, e.g.:

   WHERE LogInsertTime < DATEADD(DAY, -7, @DateTime);
   

Answer 2

I would use the following sargeable query:

SELECT * FROM MyTable WHERE LogInsertTime < DATEADD(DAY, -7, @DateTime)

The reason: I believe that the result of @DateTime-7 is not documented. Even if it just happens to be equivalent to DATEADD(DAY, -7, @DateTime), it may break in a later release.

Answer 3

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.

Demonstration:

DECLARE @MyTable TABLE(pk INT, LogInsertTime DATETIME);

INSERT @MyTable
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();

SELECT *
FROM @MyTable
WHERE DATEDIFF(DAY, LogInsertTime, @DateTime) > 7;

-- 0 records.

SELECT *
FROM @MyTable
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 0:00:00:

SELECT *
FROM @MyTable
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 LogInsertTime.