Calculating the exact date difference in years using SQL

Question

I get reports in which ETL data for the database is automatic. I extract and transform some of this data to load it somewhere else. One thing I need to do is DATEDIFF , but the year must be accurate (i.e. 4.6 years instead of rounding to five years.

Below is my script:

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

The column 'Age_In_Years' rounded. How to get the exact date in years?

Answer 1

Have you tried the difference in months and then calculated these years? For example, 30 months / 12 - 2.5 years.

Edit: This SQL query contains several approaches for calculating date differences:

 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 

Answer 2

All datediff() calculates the number of periods intersecting between dates. For example,

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

returns 1.

You will get a more accurate result if you calculate the difference between two dates in days and divide by the average length of a calendar year in days over a 400-year period (365.2425):

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

For example,

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

return 14.29461248 - just round it to the desired precision.

Answer 3

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

I know that the following script calculates the exact difference in the date (although it may not be the fastest 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 may be able to calculate small ranges with division, but why take a chance?

The following script can help test the functions of yeardiff (just swap casting (datiff (day, @ d1, @ d2) /365.2425 as int) for any function):

  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