How Can I Correctly Compare DATETIME and DATE Values in SQL Server?

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 <p>This query is SARGable, meaning it can use an index on the DateCreated column.</p><p><strong>Why Not Use Functions?</strong></p><p>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:</p>

• 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 <p>This form is compatible with all data types and precisions, regardless of the time part.</p>

The above is the detailed content of How Can I Correctly Compare DATETIME and DATE Values in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!