Best Way To Compare Dates Without Time In SQL Server

Answer :

Don't use convert - that involves strings for no reason. A trick is that a datetime is actually a numeric, and the days is the integer part (time is the decimal fraction); hence the day is the FLOOR of the value: this is then just math, not strings - much faster

declare @when datetime = GETUTCDATE() select @when -- date + time declare @day datetime = CAST(FLOOR(CAST(@when as float)) as datetime) select @day -- date only 

In your case, no need to convert back to datetime; and using a range allows the most efficent comparisons (especially if indexed):

declare @when datetime = 'Feb 15 2012  7:00:00:000PM' declare @min datetime = FLOOR(CAST(@when as float)) declare @max datetime = DATEADD(day, 1, @min)  select * from sampleTable where DateCreated >= @min and DateCreated < @max 

Simple Cast to Date will resolve the problem.

DECLARE @Date datetime = '04/01/2016 12:01:31'  DECLARE @Date2 datetime = '04/01/2016'  SELECT CAST(@Date as date)  SELECT CASE When (CAST(@Date as date) = CAST(@Date2 as date)) Then 1 Else 0 End 

SELECT ....... FROM ........ WHERE  CAST(@DATETIMEVALUE1 as DATE) = CAST(@DATETIMEVALUE2 as DATE) 

The disadvantage is that you are casting the filter column.

If there is an index on the filter column, then, since you are casting, the SQL engine can no longer use indexes to filter the date more efficiently.