[SOLVED] Calculate exact date difference in years using SQL

Calculate exact date difference in years using SQL

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?

Solution

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