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?

  • 1
    Just a question if you have a column called CreationDate, why are you not storing it as a date? – Taryn May 16 '14 at 14:31
  • It's an old standard for our software that actually stores the creation DateTime. – JustinDoesWork May 16 '14 at 14:32
  • perhaps you should try it and look at the query plan and see what's going on. – swasheck May 16 '14 at 14:33

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'

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.


| improve this answer | |
  • 1
    Great! shouldn't the last line of the example be CAST(BaseDate as date)<cast(@EndDate as date) – gordon613 Aug 28 '18 at 11:03

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))
| improve this answer | |
  • Plus 1 for the mandatory @AaronBertrand Link. – Zane May 16 '14 at 14:57

Not the answer you're looking for? Browse other questions tagged or ask your own question.