Dates while querying not returning proper output in SQL Server

Question

I have a query when I run like this

select emp_name, dt_of_join 
from emp_mst 
where dt_of_join = '2015-09-14'

I get one record from the table.

But when I try to run in dynamically like below

SELECT emp_name, Dt_Of_Join 
FROM emp_mst 
WHERE Dt_Of_Join =  DATEADD(month, -6, GETDATE())

it doesn't return any records. WHY ???

I am using SQL Server 2008.

Answer 1

Because getdate() return a time value with hours, which makes the times to be not equal. Use CAST AS DATE to trunc the date like this:

SELECT emp_name, Dt_Of_Join 
FROM emp_mst 
WHERE Dt_Of_Join =  cast(DATEADD(month, -6, GETDATE()) AS DATE)

EDIT: DATE was introduced in SQL Server 2008, if you are using an older version, try this:

SELECT emp_name, Dt_Of_Join 
FROM emp_mst 
WHERE Dt_Of_Join = CAST(FLOOR(CAST(DATEADD(month, -6, GETDATE()) AS FLOAT)) AS DATETIME)

As mentioned in the comments by @zoharpeled , you can read about the approaches to trunc a time value from dates here.