Date difference (in years) incl. year fraction

user_1350750 0
Viewed 9215

Is there a simple way to calculate the difference between two dates that:

  1. expresses it as the number of years incl. the year fraction; and
  2. takes account of leap years?

For example, the difference between 1st March 2011 and 1st March 2012 is 1 year. However, if I use DATEDIFF(day,..,..) and divide by 365 I get an (undesirable) answer of 1.00274 because of the leap year.

To be clear, I need any year fraction as well (ie. not just the whole number of years). For example the difference between 1st March 2011 and 3rd March 2012 is 1.005479 (1 year + 2/365 years). The difference between 1st March 2011 and 29th February 2012 is 0.997268 (0 year + 365/366 years)

So, in summary, the output (in DECIMAL(7,6)) from the above two examples would be:

1.000000 1.005479 0.997268

sql-server-2008 tsql sql
5 Answers
DECLARE @FromDate DATETIME = '2019-02-28', @ToDate DATETIME = '2020-02-26',
@diff_year_temp int, @diff_year int,@diff_year_frac float,@ToDateDays float,@FromDateDays float,@y_to int,@y_from int,@diff float,
@FromDec float,@JanTo float,@JanToFrac float,@FromDecFrac float,@ToDateplusone datetime,@FromplusToyear datetime

set @diff_year_temp=datediff(year,@FromDate,@ToDate)
set @FromplusToyear=case when DATEPART(day,@FromDate)=29 and datepart(month,@FromDate)=2 then dateadd(day,1,dateadd(year,@diff_year_temp,@FromDate)) else  dateadd(year,@diff_year_temp,@FromDate) end
set @ToDateplusone=dateadd(day,1,@ToDate)
set @y_to = datepart(year,@ToDate)
set @y_from=datepart(year,@FromDate)
set @FromDec=datediff(day,@FromDate,cast(CONCAT(@y_from,'-12-31') as date))+1
set @JanTo=datediff(day,cast(CONCAT(@y_to,'-01-01') as date),@ToDate)+1
set @ToDateDays = DATEDIFF(day,  cast(@y_to as char(4)),  cast(@y_to+1 as char(4))) 
set @FromDateDays = DATEDIFF(day,  cast(@y_from as char(4)),  cast(@y_from+1 as char(4)))

set @FromDecFrac=@FromDec/@FromDateDays
set @JanToFrac=@JanTo/@ToDateDays
set @diff_year=case when @ToDateplusone >= @FromplusToyear  then @diff_year_temp else @diff_year_temp-1 end
set @diff_year_frac=case when @ToDateplusone >= @FromplusToyear then datediff(day,@FromplusToyear,@ToDateplusone)/@ToDateDays  else  @JanToFrac+@FromDecFrac end
set @diff=@diff_year+@diff_year_frac

select @diff
user_70023850
Related