Calculate a Fractional Number of Years Difference Between Two Dates in SQL

Published: October 29, 2008

Hey, Mike:

Is there anything new in SQL that will calculate the difference between two dates and return a fractional number of years? For example: "2007-09-10"--"1997-01-01" should return 10.69 years.

The normal DB2 SQL function to return a difference between two timestamps (TIMESTAMP_DIFF) will not return fractional values as your calculation requires.

However, if super accuracy isn't a great need, there is an easy way to do this. Simply calculate the number of days between the two dates to get the number of days difference. Then divide the result by 365.2425, which is the average number of days in the Gregorian calendar, and you have a fractional number of years difference. For more info on using 365.2425 as the number of days in a year, click here. Other websites have the average number of days as 365.2422 although the value is gradually declining.

Here's an SQL example using host variables that rounds the result to two decimals:

Select Round(
       (Days(:EndDate)-Days(:StartDate))/365.2425
       ,2) As No_Years
  Into :NoYears
  From SysIBM/SysDummy1

And there you have it, an easy way to calculate a fractional number of years between two dates in SQL.

On a related note, this same technique can be used to get the fractional number between two months; but this is a little sloppy when using an average of 30.4369 days per month. However, starting in V6R1 the new MONTHS_BETWEEN function can be used to better estimate the fractional number of months between two dates without the SQL mess shown above. Here is an example from the IBM manual:

SELECT MONTHS_BETWEEN('2005-02-20', '2005-01-17') 
  FROM SYSIBM.SYSDUMMY1

This example will return the value 1.096774193548387.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Send your questions or comments for Michael to Ted Holt via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot