How to compare datetime with only date in SQL Server

Question

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

but in the database the user was created on 2014-02-07 12:30:47.220 and when I only put '2014-02-07'

It does not show any data

Answer 1

DON'T be tempted to do things like this:

Select * from [User] U where convert(varchar(10),U.DateCreated, 120) = '2014-02-07' 

This is a better way:

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

see: What does the word “SARGable” really mean?

EDIT + There are 2 fundamental reasons for avoiding use of functions on data in the where clause (or in join conditions).

1. In most cases using a function on data to filter or join removes the ability of the optimizer to access an index on that field, hence making the query slower (or more "costly")
2. The other is, for every row of data involved there is at least one calculation being performed. That could be adding hundreds, thousands or many millions of calculations to the query so that we can compare to a single criteria like 2014-02-07. It is far more efficient to alter the criteria to suit the data instead.

"Amending the criteria to suit the data" is my way of describing "use SARGABLE predicates"

---

And do not use between either.

the best practice with date and time ranges is to avoid BETWEEN and to always use the form:

WHERE col >= '20120101' AND col < '20120201' This form works with all types and all precisions, regardless of whether the time part is applicable.

http://sqlmag.com/t-sql/t-sql-best-practices-part-2 (Itzik Ben-Gan)