How to compare date and time with 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 just set '2014-02-07'

It does not show any data.

Answer 1

DO NOT be tempted to do such things:

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

This is the best way:

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

see Sargable (page has been removed from Wikipedia)

EDIT + There are two main reasons to avoid using functions in the data in the where clause (or in join conditions).

• In most cases, using the function for filtering or combining removes the optimizer’s ability to access the index in this field, making the query slower (or more "expensive").
• Another is that at least one calculation is performed for each data row. This can add hundreds, thousands or many millions of calculations to the query, so that we can compare them with one criterion, for example, 2014-02-07 . It’s much more efficient to change the criteria to match the data.

"Changing the criteria matching the data" is my way of describing "use SARGABLE predicates"

---

And do not use between them.

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

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

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

Answer 2

If you are using SQL Server 2008 or later, you can use the date data type:

 SELECT * FROM [User] U WHERE CAST(U.DateCreated as DATE) = '2014-02-07' 

It should be noted that if you index the date column, it will still use the index and SARGable. This is a special case for dates and times.

You can see that SQL Server actually turns this into a> and <section:

I just tried this on a large table with a secondary index in the date column according to the comments of @kobik and the index is still used, this does not apply to examples using BETWEEN or> = and <;

 SELECT * FROM [User] U WHERE CAST(U.DateCreated as DATE) = '2016-07-05' 

Answer 3

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

SQL Server compares the exact date and time ie (compare 2014-02-07 12:30:47.220 with 2014-02-07 00:00:00.000 for equality). why the comparison result is incorrect

Therefore, when comparing dates, you also need to consider time. You can use Select * from [User] U where U.DateCreated BETWEEN '2014-02-07' AND '2014-02-08' .

Answer 4

You can use the LIKE statement instead of = . But for this with DateStamp you need CONVERT first VARCHAR first:

 SELECT * FROM [User] U WHERE CONVERT(VARCHAR, U.DateCreated, 120) LIKE '2014-02-07%' 

Answer 5

Try it. This query can be used to compare dates.

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