This question already has an answer here:
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?