Calculating age over 50 years

April 11, 2024

I have a query below that is calculating age and it works.

When I add the test to print a YES/NO flag to test over 50 years I am getting a syntax error and was hoping someone could help me out.

Additionally, is there a better way to rewrite the query without doing the extract/calculation twice? Any help will be greatly appreciated.


/* WORKS */
with some_birthdays as 
 ( select date '1968-06-09' d from dual union all
  select date '1970-06-10' from dual union all
  select date '1972-06-11' from dual union all 
 select date '1974-12-11' from dual union all 
  select date '1976-09-17' from dual  ) 
 select trunc(sysdate) today  
       , d birth_date
      , extract(year from numtoyminterval(months_between(trunc(sysdate),d),'month')) age
   from some_birthdays 
/

/* FAILS*/

with some_birthdays as 
 ( select date '1968-06-09' d from dual union all
  select date '1970-06-10' from dual union all
  select date '1972-06-11' from dual union all 
 select date '1974-12-11' from dual union all 
  select date '1976-09-17' from dual  ) 
 select trunc(sysdate) today  
       , d birth_date
      , extract(year from numtoyminterval(months_between(trunc(sysdate),d),'month')) age,
CASE WHEN extract(year from numtoyminterval(months_between(trunc(sysdate),d),'month')) < 50
THEN 'NO' ELSE 'YES' END AS 'OVER_50' 
   from some_birthdays 
/

>Solution :

Remove the single quotes around OVER_50 – it’s a column name / identifier, not a string. To not repeat the extract, simply use an inline view (nested select) to calculate age first, then you can reuse age multiple times in a parent block. Also you don’t need to use EXTRACT or NUMTOYMINTERVAL at all – just convert from months to years using simple math:

 with some_birthdays as 
 ( select date '1968-06-09' d from dual union all
  select date '1970-06-10' from dual union all
  select date '1972-06-11' from dual union all 
 select date '1974-12-11' from dual union all 
  select date '1976-09-17' from dual  ) 
 select trunc(sysdate) today  
       , d birth_date
      , age,
        CASE WHEN age < 50
        THEN 'NO' ELSE 'YES' END AS OVER_50
   from (SELECT d, 
                FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE),d)/12) age
           FROM some_birthdays)