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

Question

I have a table TEST with a DATETIME field, like this:

ID NAME DATE 1 TESTING 2014-03-19 20:05:20.000 

What I need a query returning this row and every row with date 03/19/2014, no matter what the time is. I tried using

select * from test where date = '03/19/2014'; 

But it returns no rows. The only way to make it work that I found is to also provide the time portion of the date:

select * from test where date = '03/19/2014 20:03:02.000'; 

Answer 1

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'