Datediff | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Datediff


Hi, I have a table EmployeeMaster which has the field Joined_Date. I want to retrieve the records of employees from EmployeeMaster whose Joined_Date is more than 1 year from today.
What is the query to be used? I used the query select * from EmployeeMaster where getdate()-Joined_Date>365
which gives the expected result. But when I executed
select Joined_date,getdate()-Joined_date as Difference from EmployeeMaster
that returned the dates back to years 1900, 1899, etc.
Here are some results.
Joined_Date Difference
2002-03-19 00:00:00.000 1902-08-26 14:59:06.347
2002-04-17 00:00:00.000 1902-07-28 14:59:06.347
2004-12-03 00:00:00.000 1899-12-09 14:59:06.347
2002-12-03 00:00:00.000 1901-12-10 14:59:06.347
2004-12-03 00:00:00.000 1899-12-09 14:59:06.347
2005-12-03 00:00:00.000 1898-12-09 14:59:06.347
2006-12-03 00:00:00.000 1897-12-09 14:59:06.347
2003-12-13 00:00:00.000 1900-11-30 14:59:06.347
2002-12-03 00:00:00.000 1901-12-10 14:59:06.347 What is the logic? Madhivanan
You can’t do calculations directly on dates: use DATEDIFF to evaluate two dates, and DATEADD to calculate a new date. Look this up in BOL. SELECT *
FROM EmployeeMaster
WHERE DATEDIFF(d, Joined_Date, GETDATE()) > 365 or SELECT *
FROM EmployeeMaster
WHERE DATEDIFF(d, GETDATE(), Joined_Date) < -365
<br />You need to use the SQL function DATEDIFF. Details of which can be found in BOL. The syntax is <br /><br />DATEDIFF(<i>datepart</i>, <i>Date1</i>, <i>Date2</i>)<br /><br />So <br />SELECT DATEDIFF(dd, ’10 Nov 2004′, ’12 Nov 2004′)<br /><br />will return 2<br /><br />BOL will tell you all dateparts etc. However you must beware that SQL counts date boundaries crossed<br /><br />e.g.<br /><br />SELECT DATEDIFF(yy, ’10 dec 2004′, ’12 jan 2005′)<br /><br />will return 1 as you have crossed a year boundary even though a full year has not passed, so use it with care.<br /><br />Regards,<br /><br />Robert.<br /><br />PS This should probably have been posted under a different topic as it is not really DBA question – Satya will get ya. [<img src=’/community/emoticons/emotion-5.gif’ alt=’;)‘ />]<br /><br />
Ah, and if performance isn’t too good (large number of records) then you need to keep Joined_Date out of the calculation, like so: SELECT *
FROM EmployeeMaster
WHERE Joined_Date < DATEADD(yyyy, -1, GETDATE()) [Edit: use yyyy instead of single y.]

Thanks for your responses. Madhivanan
]]>