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.

GetDate() returns a datetime instead of a date. Is Dt_Of_Join a datetime field filled with only dates? You could try the following: DATEADD(month, -6, cast(GETDATE() as date)) — Me.Name
– Me.Name, Commented Mar 14, 2016 at 12:47
Also, you might want to do a select getdate() just to verify that the date is correct on your server. — Zohar Peled
– Zohar Peled, Commented Mar 14, 2016 at 12:47
@Me.Name: Dt_Of_Join is datetime dataype and ur solution also didn't worked giving error as Type date is not a defined system type. — HEEN
– HEEN, Commented Mar 14, 2016 at 12:49
@ZoharPeled: after your suggestion it gives as 2016-03-14 18:19:29.993 i guess it is working — HEEN
– HEEN, Commented Mar 14, 2016 at 12:50
If the row you get back with 2015-09-14 has a time between 00:00:00 and 18:19:29 @Me.Name is probably right. You have to keep in mind, that DATETIME values carry a time and today at 18:19 minus 6 months is then at 18:19... — Gottfried Lesigang
– Gottfried Lesigang, Commented Mar 14, 2016 at 12:53

Community · Accepted Answer · 2017-05-23 12:07:50Z

4

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.

CommunityBot

11 silver badge

answered Mar 14, 2016 at 12:48

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

getting error as Type DATE is not a defined system type.

yes, tried that and is working, but what is FLOOR and why FLOAT is used ?

Its another way of triming the time value, what error are you getting? @coder

no, not any error now, It is working fine and expected output is coming. Thanks a lot

You might also want to add a link to this.