how to query where date with time = date without time in ms sql

Question

I want to do a query with dates this is my sample tsql:

select * from Bookings where StartTime = '2/15/2014'

the starttime has value '2/15/2014 12:00:00 AM'

when I query where StartTime = date with no time the result is 0

Anybody can help how to do this?

thanks

Answer 1

The best way to do this is with a simple comparison:

select *
from Bookings
where StartTime >= cast('2014-02-15' as date) and StartTime < cast('2014-02-14' as date);

This is the safest method of comparison, because it will take advantage of an index on StartTime. This property is called "sargability".

In SQL Server, casting to a date should also be sargable, so you could also do:

select *
from Bookings
where cast(StartTime as date) = cast('2014-02-15' as date) ;

Answer 2

Try like this

SELECT * FROM  Bookings WHERE Convert(VARCHAR(10),StartTime,101) =  Convert(Varchar(10),'2/15/2014',101)

If you are using SQL SERVER 2012

Try this

 SELECT * FROM  Bookings WHERE FORMAT(StartTime,'M/dd/yyyy') = FORMAT('2/15/2014','M/dd/yyyy')

SQL FORMAT