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

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.

enter image description here

You can see that SQL Server actually turns this into a > and < 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 <:

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

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

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

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

[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