Date difference (in years) incl. year fraction

sql, sql-server-2008, t-sql

Solution

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)

Problem

Is there a simple way to calculate the difference between two dates that: - expresses it as the number of years incl. the year fraction; and - 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

Original source