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

This works for me for MS SQL server:

select * from test
where 
year(date) = 2015
and month(date) = 10
and day(date)= 28 ;

use range, or DateDiff function

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

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

Other options are:

If you have control over the database schema, and you don't need the time data, take it out.
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'

I am using MySQL 5.6 and there is a DATE function to extract only the date part from date time. So the simple solution to the question is -

 select * from test where DATE(date) = '2014-03-19';

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html

Simple answer;

select * from test where cast ([date] as date) = '03/19/2014';

Related