Best way to compare dates without time in SQL Server

Question

select * from sampleTable  where CONVERT(VARCHAR(20),DateCreated,101)  =     CONVERT(VARCHAR(20),CAST('Feb 15 2012  7:00:00:000PM' AS DATETIME),101) 

I want to compare date without time

Is above query is ok? or other better solution you suggest

• I am using SQL Server 2005
• Date saved in UTC format on server
• Users against this data belongs different timezone

Answer 1

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 

Answer 2

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