Advertisement
Advertisement


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

2015/10/15
1
75
10/15/2015 2:50:57 AM


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:

enter image description here

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'

showing index usage with secondary index

2016/07/05

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'.

2015/08/05

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' 
2018/11/12

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%'
2014/08/29

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'
2015/02/19