How to query DATETIME field using only date in Microsoft SQL Server?

use range, or DateDiff function

 select * from test 
 where date between '03/19/2014' and '03/19/2014 23:59:59'

or

 select * from test 
 where datediff(day, date, '03/19/2014') = 0

Other options are:

1. If you have control over the database schema, and you don’t need the
   time data, take it out.

2. or, if you must keep it, add a computed column attribute that has the time portion of the date value stripped off…

Alter table Test
Add DateOnly As
DateAdd(day, datediff(day, 0, date), 0)

or, in more recent versions of SQL Server…

Alter table Test
Add DateOnly As
Cast(DateAdd(day, datediff(day, 0, date), 0) as Date)

then, you can write your query as simply:

select * from test 
where DateOnly = '03/19/2014'