Does CAST(DateTime AS DATE) cause the entire table to be CAST before comparison?

Question

This is a very common need in the WHERE clause of our SQL statements:

 CAST(Something.CreationDate AS DATE) BETWEEN CAST(@firstDate AS DATE)
 AND CAST(@lastDate AS DATE)

All of the fields are of type DateTime that are cast to DATE. Does this cause a conversion to run across the entire table first and then comparisons happens or is this very similar to a scan on that table?

Answer 1

Whipped up a simple example on this using a handy calendar table.

DECLARE @StartDate DATETIME = '2014-04-16 09:35:36.510'
DECLARE @EndDate DATETIME = '2014-05-16 09:35:36.510'


select
    *
from Calendar
where CAST(BaseDate AS date) > CAST(@StartDate AS date)
    AND CAST(BaseDate AS date) > CAST(@StartDate AS date)

When we look at the query plan for this we can see it's doing a simple index seek and all is well.

Answer 2

I think that in 2012 version (or earlier) an optimizer improvement made this cast especially not to do a conversion.

But it's always safe to use open-closed ranges for this type of queries, instead of the evil BETWEEN (see Aaron Bertrand's blog for more details). No conversions of the CreationDate will be needed for this condition and indexes will be available for use:

WHERE Something.CreationDate >= CAST(@firstDate AS DATE)
  AND Something.CreationDate < DATEADD( day, 1 CAST(@lastDate AS DATE))