I have a Employee table like below
ID Name Mobile Ondate Address
1 Ankit 1234567895 2016-11-08 10:10:04.540 abc
2 Amit 4521545258 2016-11-08 11:10:04.540 bcd
3 Amit2 7541258562 2016-11-08 12:10:04.540 gfd
select * from Employee where convert(date,ondate)='2016-11-08 12:10:04.540'
select * from Employee where convert(date,ondate)=getdate()
This is data type precedence at work. In your first query, in the
WHERE clause you have a
date on one side of a comparison and a
varchar on the other.
date wins, your string is converted to a
date, the time is ignored and every row matches.
In your second query, you have a
date on one side of the comparison and a
datetime on the other side.
datetime wins, the
date is converted (back) into a
datetime, and the datetimes don't match on their time components.
If you want to select values for today, use something like:
select * from Employee where ondate >= DATEADD(day,DATEDIFF(day,0,GETDATE()),0) and ondate < DATEADD(day,DATEDIFF(day,0,GETDATE()),1)
DATEDIFF expressions are effectively computing "midnight at the start of today" and "midnight at the start of tomorrow". Both expressions will be computed once, and any index on the
ondate column can then be used, if one exists, and we avoid excessively transforming column data.