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

Question

I want to make a request with dates, this is my tsql example:

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

start time is '2/15/2014 12:00:00 AM'

when I ask where StartTime = date without time, the result is 0

Can anyone help how to do this?

thanks

Answer 1

try it

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

If you are using SQL SERVER 2012

try it

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

SQL FORMAT

Answer 2

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 comparison method because it will use an index on StartTime . This property is called mobility.

In SQL Server, casting in date must also be compatible, so you can also:

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

Answer 3

'2/15/2014' can be interpreted differently depending on your language. Try using the ISO date letter '2014-02-15', which is independent of the locale.

 select * from Bookings where StartTime = '2014-02-15' 

Or, if StartTime includes a clock:

 select * from Bookings where StartTime >= '2014-02-15' and StartTime < '2014-02'16' 

Answer 4

I believe you can do this too:

 select * from Bookings where StartTime::date = '2014-2-15'