Search code examples
sqlsql-serverdatetimeutc

Best way to compare dates without time in SQL Server

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
Solution

  • 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