What is the most accurate way of using DATEDIFF in SQL Server?

Question

I have two computed columns (MonthsInService and YearsInService) with the following expressions.

MonthsInService = (datediff(month,[DateEngaged],getdate()))

YearsInService = (datediff(month,[DateEngaged],getdate())/(12))

Now if for example DateEngaged = 2012-April-09 and getdate() is 2013-April-08, MonthsInService returns 12 and YearsInService is 1.

My application requires that YearsInService be Zero since there is still one day to go before the employees first Anniversary.

Am not even sure how to best handle the MonthsInService column since months have varying number of days.

Answer 1

Via using day you can reach the result:

select 
datediff(month,'2012-April-09','2013-April-08') MonthsInService
,datediff(day,'2012-April-09','2013-April-08')/365 YearsInService

Output:

12  0  

or use function for maximum precision:

CREATE FUNCTION [dbo].[getFullYears] 
(
    @dateX datetime,
    @dateY datetime
)  
RETURNS int
AS  
BEGIN 
    DECLARE @y int
    SET @y =DATEDIFF(year,@dateX,@dateY)
    IF (@dateY < DATEADD(year, @y, @dateX)) SET @y = @y -1
    RETURN @y
END

select dbo.getFullYears('2012-April-09','2013-April-09') --1
select dbo.getFullYears('2012-April-09','2013-April-08') --0

For months calculation you can refer here: Calculating number of full months between two dates in SQL