CODEWITHSUNDEEP
sqlsql-serverdatabase
Jason596
Jason596

Reputation: 43

Different SQL query to compare date

I try to grab records from the email table which is less than 14 days. Just want to know if there is a performance difference in the following two queries?

  select * 
  from email e
  where  DATEDIFF(day, e.recevied_date, cast(GETDATE() as date)) < 14

  select * 
  from email e
  where (GETDATE() - e.recevied_date) < 14

Upvotes: 0

Views: 99

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

The two expressions are not equivalent.

The first counts the number of midnights between the two dates, so complete days are returned.

The second incorporates the time.

If you want complete days since so many days ago, the best method is:

where e.received_date >= convert(date, dateadd(day, -14, getdate()))

The >= is to include midnight.

This is better because the only operations are on getdate() -- and these can actually be handled prior to the main execution phase. This allows the query to take advantage of indexes, partitions, and statistics on e.received_date.

Upvotes: 0

allmhuran
allmhuran

Reputation: 4454

A sargable way of writing this predicate - ie, so SQL Server can use an index on the e.received_date column, would be:

where e.received_date > dateadd(day, -14, getdate())

With this construction there is no expression that needs to be evaluated for the data in the received_date column, and right hand side evaluates to a constant expression.

If you put the column into an expression, like datediff(day, e.received_date, getdate()) then SQL server has to evaluate every row in the table before being able to tell whether or not it is less than 14. This precludes the use of an index.

So, there should be virtually no significant difference between the two constructions you currently have, in the sense that both will be much slower than the sargable predicate, especially if there is an index on the received_date column.

Upvotes: 3

Related Questions