3

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nad
  • 4,605
  • 11
  • 71
  • 160
  • 2
    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 Mar 14 '16 at 12:47
  • 1
    Also, you might want to do a `select getdate()` just to verify that the date is correct on your server. – Zohar Peled Mar 14 '16 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.` – Nad Mar 14 '16 at 12:49
  • @ZoharPeled: after your suggestion it gives as `2016-03-14 18:19:29.993` i guess it is working – Nad Mar 14 '16 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... – Shnugo Mar 14 '16 at 12:53

1 Answers1

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.

Community
  • 1
  • 1
sagi
  • 40,026
  • 6
  • 59
  • 84
  • getting error as `Type DATE is not a defined system type.` – Nad Mar 14 '16 at 12:49
  • yes, tried that and is working, but what is `FLOOR` and why `FLOAT` is used ? – Nad Mar 14 '16 at 12:53
  • Its another way of triming the time value, what error are you getting? @coder – sagi Mar 14 '16 at 12:53
  • no, not any error now, It is working fine and expected output is coming. Thanks a lot – Nad Mar 14 '16 at 12:54
  • You might also want to add a link to [this.](http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server) – Zohar Peled Mar 14 '16 at 12:56