ORACLE Age Calculation from birthday (AskTOM)

ageSQLcalculation.sql

-- https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9531934000346471628

-- Cálculo edad / edat / age

-- Age calculation from birthday(always correct!!!!)
-- ==============================
-- NOTA: tras varias pruebas lo siguiente se entiende mejor y es correcto:
trunc(MONTHS_BETWEEN(TO_DATE(20200229,'yyyymmdd'), TO_DATE(20000229,'yyyymmdd'))/12)
--trunc() o floor()

--version anterior: trunc((to_number(to_char(now,'YYYYMMDD'))- to_number(to_char(birth,'YYYYMMDD')))/10000) 
/*
We are converting the dates to YYYYxxxx, so the years are in effect multiples of 10,000 (because the 'xxxx' is capped at 9999). 
The 'xxxx' (ie months/days) becomes the means via which we decide on boundary points, that is, 
when comparing YYYYxxxx to ZZZZwwww, we only need to care about whether the 'xxxx' or greater or less than 'wwww'
to decide whether a year has "ticked over". This way, we dont need to worry about leap years etc etc

                                                     
DEMONSTRATION
=============
SQL>
SQL> select
  2  x.*,
  3   case when expected = c1 then 'OK' else 'ERROR' end tag
  4  from
  5  (
  6  select
  7    t.*,
  8    trunc((to_number(to_char(now,'YYYYMMDD'))-
  9          to_number(to_char(birth,'YYYYMMDD')))/10000) c1
 10  from t
 11  ) x;

BIRTH     NOW         EXPECTED         C1 TAG
--------- --------- ---------- ---------- -----
12-MAY-00 30-MAR-16         15         15 OK
12-MAY-00 30-MAY-16         16         16 OK
12-MAY-00 12-MAY-16         16         16 OK
12-MAY-00 12-JUN-76         76         76 OK
29-FEB-00 01-MAR-13         13         13 OK
29-FEB-00 29-FEB-16         16         16 OK
29-FEB-00 28-FEB-17         16         16 OK
29-FEB-00 28-FEB-87         86         86 OK
01-JAN-00 31-DEC-87         87         87 OK
*/

-- Do NOT use
-- ==========
trunc((now-birth)/365)