sql server - Calculate exact date difference in years using SQL

Question

I receive reports in which the data is ETL to the DB automatically. I extract and transform some of that data to load it somewhere else. One thing I need to do is a DATEDIFF but the year needs to be exact (i.e., 4.6 years instead of rounding up to five years.

The following is my script:

select *, DATEDIFF (yy, Begin_date, GETDATE()) AS 'Age in Years'
from Report_Stage;

The 'Age_In_Years' column is being rounded. How do I get the exact date in years?

Answer 1

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.