Andrew Fraser DBA

SQL to display date duration as number of years and months

Convert date duration (here customer age at time of first application) in years and months, e.g. ‘3408’ for 34 years and 8 months old:

  WITH customers AS ( SELECT SYSDATE AS appl_date , TO_DATE ( '01-AUG-1980' , 'DD-MON-YYYY' ) AS date_of_birth FROM DUAL )
SELECT TO_CHAR ( GREATEST ( 0 , LEAST ( 99 , FLOOR ( ( c.appl_date - c.date_of_birth ) / 365.25 ) ) ) , 'fm00' )  -- years
          || TO_CHAR ( GREATEST ( 0 , FLOOR ( 12 * MOD ( ( ( c.appl_date - c.date_of_birth ) / 365.25 ) , 1 ) ) ) , 'fm00' ) AS years_months
     , ( c.appl_date - c.date_of_birth ) / 365.25 AS years
     , 12 * MOD ( ( ( c.appl_date - c.date_of_birth ) / 365.25 ) , 1 ) AS months
  FROM customers c ;