Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)?

share|improve this question
    
What type LogInsertTime is? –  dezso Oct 17 '12 at 15:18
1  
Take a look knowdotnet.com/articles/getdatereturn.html . –  a1ex07 Oct 17 '12 at 15:43
    
LogInsertTime is a DATETIME –  Alf47 Oct 17 '12 at 18:37

3 Answers 3

up vote 15 down vote accepted

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);
    
share|improve this answer
    
I agree, my goal was to change query 1 into something similar to query 2 so the indexes could be effectively used. Thank you for your help –  Alf47 Oct 17 '12 at 15:46

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.

share|improve this answer

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.

share|improve this answer
    
Great that is exactly what I was looking for, thank you –  Alf47 Oct 17 '12 at 15:47
2  
It is, in fact, documented and well-defined: - (Subtract): Subtracts two numbers (an arithmetic subtraction operator). Can also subtract a number, in days, from a date.. Still, I agree that using explicit date functions makes the resulting query more readable and maintainable than "arithmetic operator magic". –  Heinzi Oct 18 '12 at 6:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.