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

User · Answer

Of-course this is an old thread but to make it complete.

From SQL 2008 you can use DATE datatype so you can simply do:

SELECT CONVERT(DATE,GETDATE())

OR

Select * from [User] U
where  CONVERT(DATE,U.DateCreated) = '2014-02-07'

User · Answer

If you are on SQL Server 2008 or later you can use the date datatype   SELECT   FROM  User  U WHERE CAST U DateCreated as DATE     2014-02-07    It should be noted that if date column is indexed then this will still utilise the index and is SARGable  This is a special case for dates and datetimes     You can see that SQL Server actually turns this into a   and  lt  clause        I ve just tried this on a large table  with a secondary index on the date column as per  kobik s comments and the index is still used  this is not the case for the examples that use BETWEEN or    and  lt     SELECT   FROM  User  U WHERE CAST U DateCreated as DATE     2016-07-05

User · Answer

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  gt    2014-02-07  and U DateCreated  lt  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     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   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  lt   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

User · Answer

Please try this  This query can be used for date comparison  select   from  User  U where convert varchar 10  U DateCreated  120     2014-02-07

User · Answer

You can use LIKE statement instead of    But to do this with DateStamp you need to CONVERT it first to VARCHAR   SELECT    FROM  User  U WHERE CONVERT VARCHAR  U DateCreated  120  LIKE  2014-02-07

User · Answer

According to your query     Select   from  User  U where U DateCreated    2014-02-07     SQL Server is comparing exact date and time i e  comparing 2014-02-07 12 30 47 220 with 2014-02-07 00 00 00 000 for equality   that s why result of comparison is false  Therefore  While comparing dates you need to consider time also  You can use     Select   from  User  U where U DateCreated BETWEEN  2014-02-07  AND  2014-02-08

[sql] How to compare datetime with only date in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to datetime