Finding data for the current date in SQL Server

Question

Why does the first query take less time than the second in SQL Server?

This request takes 4 seconds:

select *
from salesinvmaster
where day(salesdate) = day(getdate()) and
      month(salesdate) = month(getdate()) and
      year(salesdate) = year(getdate())

This request takes 10 seconds:

select *
from salesinvmaster
where salesdate between '2017-11-01 00:00:00' and '2017-11-01 23:59:59'

Answer 1

The two requests are different from each other, because today is one day in December, not November 1.

I assume that you do not have an index in the column salesdate, and that the first query returns fewer rows - hence it looks faster. For the record, I would recommend writing the logic as one of the following:

where convert(date, salesdate) = convert(date, getdate())

where salesdate >= convert(date, getdate()) and
      salesdate < dateadd(day, 1, convert(date, getdate()))

Note that SQL Server uses an index to convert date / time to date. This is one of the rare (only?) Times that a function does not prevent the use of an index.

, .

Answer 2

Exeuction . , !