Comparing converted dates in SQL Server 2008

Question

i am trying to compare dates. The comparison is working fine with < and > signs but when i use = its returning zero rows. The query is as follows:

SET DateFormat DMY 
SELECT * FROM [Users]  
where name like '%' + replace('', '%', '[%]') + '%'
and email like '%' + replace('', '%', '[%]') + '%' 
and last_edited = CAST('18/3/2014' AS DATETIME) 
ORDER BY CONVERT(DateTime, Last_edited,101)  desc

what is wrong with this query in "=" case ?`

Answer 1

Change your where clause to remove the time portion:

DATEADD(dd, DATEDIFF(dd, 0, [last_edited]), 0) = CAST('18/3/2014' AS DATETIME) 

Based on the column name, I am assuming that [last_edited] has values like this 2014-03-18 17:50:08.000, so you need to remove the time if you want to do an exact comparison with 18/3/2014.