Calculate exact date difference in years using SQL

Have you tried getting the difference in months instead and then calculating the years that way? For example 30 months / 12 would be 2.5 years.

Edit: This SQL query contains several approaches to calculate the date difference:

SELECT CONVERT(date, GetDate() - 912) AS calcDate
      ,DATEDIFF(DAY, GetDate() - 912, GetDate()) diffDays
      ,DATEDIFF(DAY, GetDate() - 912, GetDate()) / 365.0 diffDaysCalc
      ,DATEDIFF(MONTH, GetDate() - 912, GetDate()) diffMonths
      ,DATEDIFF(MONTH, GetDate() - 912, GetDate()) / 12.0 diffMonthsCalc
      ,DATEDIFF(YEAR, GetDate() - 912, GetDate()) diffYears

I think that division by 365.2425 is not a good way to do this. No division can to this completely accurately (using 365.25 also has issues).

I know the following script calculates an accurate date difference (though might not be the most speedy way):

        declare @d1 datetime ,@d2 datetime
        --set your dates eg: 
        select @d1 = '1901-03-02'
        select @d2 = '2016-03-01'

        select DATEDIFF(yy, @d1, @d2) -
            CASE WHEN MONTH(@d2) < MONTH(@d1) THEN 1
                 WHEN MONTH(@d2) > MONTH(@d1) THEN 0
                 WHEN DAY(@d2) < DAY(@d1) THEN 1
                 ELSE 0 END

         -- = 114 years

For comparison:

         select datediff(day,@d1 ,@d2) / 365.2425
         -- = 115 years => wrong!

You might be able to calculate small ranges with division, but why take a chance??

The following script can help to test yeardiff functions (just swap cast(datediff(day,@d1,@d2) / 365.2425 as int) to whatever the function is):

   declare @d1 datetime set @d1 = '1900-01-01'

   while(@d1 < '2016-01-01')
   begin
    declare @d2 datetime set @d2 = '2016-04-01'

    while(@d2 >= '1900-01-01')
    begin
        if (@d1 <= @d2 and dateadd(YEAR,     cast(datediff(day,@d1,@d2) / 365.2425 as int)      , @d1) > @d2)
        begin
            select 'not a year!!', @d1, @d2, cast(datediff(day,@d1,@d2) / 365.2425 as int)
        end

        set @d2 = dateadd(day,-1,@d2)
    end

    set @d1 = dateadd(day,1,@d1)
  end

All datediff() does is compute the number of period boundaries crossed between two dates. For instance

datediff(yy,'31 Dec 2013','1 Jan 2014')

returns 1.

You'll get a more accurate result if you compute the difference between the two dates in days and divide by the mean length of a calendar year in days over a 400 year span (365.2425):

datediff(day,{start-date},{end-date},) / 365.2425

For instance,

select datediff(day,'1 Jan 2000' ,'18 April 2014') / 365.2425

return 14.29461248 — just round it to the desired precision.

You want the years difference, but reduced by 1 when the "day of the year" of the future date is less than that of the past date. So like this:

SELECT *
,DATEDIFF(YEAR, [Begin_date], [End_Date])
 + CASE WHEN CAST(DATENAME(DAYOFYEAR, [End_Date]) AS INT)
          >= CAST(DATENAME(DAYOFYEAR, [Begin_date]) AS INT)
   THEN 0 ELSE -1 END
 AS 'Age in Years'
from [myTable];