Comparing Datetime with Date in SQL Server

When comparing a datetime value with only a date, the result may be unexpected. This is because the datetime data type includes both date and time components. For instance, if you have a user table with a DateCreated column of datetime type, the following query:

Select * from [User] U
where  U.DateCreated = '2014-02-07'     

will not return any records, even though the user was created on 2014-02-07 at 12:30:47.220.

To accurately compare a datetime with only a date, use the following method:

Select * from [User] U 
where U.DateCreated >= '2014-02-07' and U.DateCreated < dateadd(day,1,'2014-02-07')

This query is SARGable, meaning it can use an index on the DateCreated column.

Why Not Use Functions?

It may be tempting to use functions like CONVERT to extract the date component from the datetime. However, this is not recommended. Using functions in the WHERE clause or join conditions:

• Removes the ability of the optimizer to use an index on the field
• Adds unnecessary calculations for each row of data

Avoiding BETWEEN

BETWEEN should also be avoided when dealing with date and time ranges. Use the following form instead:

WHERE col >= '20120101' AND col < '20120201'

This form is compatible with all data types and precisions, regardless of the time part.