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.

What happens if you `DATEDIFF` with `MONTH` for ... months and `YEAR` for ... years? Honest question. — ta.speot.is, Apr 08 '13 at 11:23
But should months in service (for your example) be 12 (that you've not objected to) or should it be 11? — Damien_The_Unbeliever, Apr 08 '13 at 13:32
Check function from answer (for years) and linked question for months - both should work. — revoua, Apr 08 '13 at 13:32
try checking it via year 'yy' or day 'dd'. https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017 — Channa, Jun 30 '19 at 17:56

score 6 · Answer 1 · answered Apr 08 '13 at 13:39

Unfortunately, DATEDIFF computes the number of transitions of the element, rather than the usual, human intuition of the difference between two dates (e.g. DATEDIFF(year,'20121231','20130101') is 1, even though not many people would say that there's a difference of a year).

The solution I'd use is a bit repetitive, but doesn't need a separate function, and always gets e.g. leap years correct:

declare @T table (
    DateEngaged datetime not null,
    MonthsInService as CASE
        WHEN DATEADD(month,DATEDIFF(month,DateEngaged,GETDATE()),DateEngaged) > GETDATE()
            THEN DATEDIFF(month,DateEngaged,GETDATE()) - 1
            ELSE DATEDIFF(month,DateEngaged,GETDATE())
        END,
    YearsInService as CASE
        WHEN DATEADD(year,DATEDIFF(year,DateEngaged,GETDATE()),DateEngaged) > GETDATE()
            THEN DATEDIFF(year,DateEngaged,GETDATE()) - 1
            ELSE DATEDIFF(year,DateEngaged,GETDATE())
        END
)

insert into @T (DateEngaged) values ('20120409'),('20120408')

select * from @T

Produces:

DateEngaged             MonthsInService YearsInService
----------------------- --------------- --------------
2012-04-09 00:00:00.000 11              0
2012-04-08 00:00:00.000 12              1

It works by asking "If we take the naive answer produced by DATEDIFF, does it given an answer that's too high by 1?" - and if so, we just subtract one from the answer it gives. DATEDIFF should only ever be over by 1.

This is much better than the divide by 365 answer. – Doctor Jones Oct 31 '13 at 12:56 — Doctor Jones, Oct 31 '13 at 12:56

score 1 · Accepted Answer · edited May 23 '17 at 12:22

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

edited May 23 '17 at 12:22

Community

1
1

answered Apr 08 '13 at 10:59

revoua

2,044
1
21
28

2

This doesn't account for a leap year. – Tanner Apr 08 '13 at 11:10
@Tanner added function for esthete :) – revoua Apr 08 '13 at 11:28

score 0 · Answer 3 · edited Oct 26 '21 at 05:43

0

Try this query :

DATEDIFF(DAY, CONVERT(date, dtmDOB),                         
  CONVERT(date, GETDATE()))*(12.0/365.25)),1))                      
  AS TotalMonths,

edited Oct 26 '21 at 05:43

Amirhossein

1,148
3
15
34

answered Oct 25 '21 at 18:30

Hemant Acharya

11
4

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

3 Answers3