Nitin Kumar Nitin Kumar - 28 days ago 9
SQL Question

Sql select query confusion when passing getdate() & its value to where condition

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


Now i write select query like below then it gives all records of Employee table

select * from Employee where convert(date,ondate)='2016-11-08 12:10:04.540'


but when i pass
getdate()
direct in where condition then it gives empty

select * from Employee where convert(date,ondate)=getdate()


while
select getdate()
result is
2016-11-08 12:10:04.540


so please give proper reason about it.

Answer

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)

Where the DATEADD/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.

Comments