Calculate age from two-digit year date, Oracle

Language: Layout:

with bilägare as
(
  select 'John' as fnamn, 'Smith' as enamn, '181003' as pnr from dual union all
  select 'Jane' as fnamn, 'Murray' as enamn, '181004' as pnr from dual union all
  select 'James' as fnamn, 'Miller' as enamn, '181005' as pnr from dual union all
  select 'Lilly' as fnamn, 'Jones' as enamn, '170803' as pnr from dual union all
  select 'Bob' as fnamn, 'Tailor' as enamn, '171105' as pnr from dual union all
  select 'Rex' as fnamn, 'Garland' as enamn, '470115' as pnr from dual
)
, proper as
(
	select
		fnamn,
		enamn,
		case when to_date(substr(pnr, 1, 6), 'yymmdd') >= trunc(sysdate) 
			then to_date(substr(pnr, 1, 6), 'yymmdd') - interval '100' year(3)
			else to_date(substr(pnr, 1, 6), 'yymmdd')
		end as birthdate
	from bilägare
)
select
  fnamn,
  enamn,
  birthdate,
  extract(year from sysdate) - extract(year from birthdate)
  - case when extract(year from sysdate) > extract(year from birthdate)
          and to_char(sysdate, 'mmdd') > to_char(birthdate, 'mmdd')
      then 1 else 0 
    end as age_years,
  case when extract(month from birthdate) >= extract(month from sysdate) 
    then extract(month from birthdate) - extract(month from sysdate)
    else 12 + extract(month from birthdate) - extract(month from sysdate)
  end as age_months,
  trunc(months_between (sysdate, birthdate), 2) as diff
from proper


absolute service time: 0,91 sec