Searching data for current date in SQL Server

Question

Why is the first query taking less time than the second one in SQL Server?

This query takes 4 seconds to complete:

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

This query 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 queries are different, because today is some day in December, not November 1st.

My guess is that you do not have an index on the salesdate column, and that the first query is returning 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 does use an index for the conversion of a date/time value to a date. This is one of the rare (only?) times when a function does not prevent the use of an index.

As for the second method, it dispenses with the need to include the time component of the values.

Answer 2

Check the Exeuction plan for the query i think We've got implicit conversions on the date! check this