Date difference (in years) incl. year fraction

Question

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

Answer 1

Here is a query. But there is a logical error in your example. I think "difference between 1st March 2011 and 3rd March 2012 is 1.005479 (1 year + 2/365 years)" should be (1 year + 2/366 years) because 29.02.2012 in between 3rd March 2011 and 3rd March 2012 so in the last year 366 days.

    Declare @BDate datetime
    Declare @EDate datetime
    SET @BDate='2011-03-01'
    SET @EDate='2012-02-29'

    select 

    datediff(year,@BDate,@Edate)- 
    case when dateadd(year,datediff(year,@BDate,@Edate),@BDate)>@Edate then 1 else 0 end 
    +cast(datediff(day,dateadd(year,datediff(year,@BDate,@Edate)-
    case when dateadd(year,datediff(year,@BDate,@Edate),@BDate)>@Edate then 1 else 0 end   ,@BDate),@Edate) as float)/
cast(datediff(day,dateadd(year,-1,@Edate),@Edate) as float)

Answer 2

I think this works (I've hopefully picked names you can follow):

declare @StartTime datetime
declare @EndTime datetime

select @StartTime = '20110301',@EndTime = '20120303'

select YearsDiffNorm + ((DaysIntoYear * 1.0) / (DaysIntoYear + DaysRemainingInYear))
from (
select
    YearsDiffNorm,
    DATEDIFF(day,DATEADD(YEAR,YearsDiffNorm,@StartTime),@EndTime) as DaysIntoYear,
    DATEDIFF(day,@EndTime,DATEADD(YEAR,YearsDiffNorm+1,@StartTime)) as DaysRemainingInYear
from (
select CASE WHEN DATEADD(year,YearsDiff,@StartTime) > @EndTime then YearsDiff - 1 else YearsDiff END as YearsDiffNorm
from (
    select DATEDIFF(year,@StartTime,@EndTime) as YearsDiff
) t
) t2
) t3