Calculate age from date of birth with formatting

I would like to calculate the age from the date of birth stored as a string using the format in the comments in the code. Here’s my code:

--pnr values
--'19490321-7000'
--'19540201-7000'
--'19650823-7000'

declare
v_now date :=to_date(sysdate, 'YYYY-MM-DD');
v_dob date;
v_age number;
cursor c_carowners is select fnamn, enamn, pnr
                      from carowners;
begin
  for v_rec in c_carowners loop

    v_dob:= to_date(substr(v_rec.pnr,1,8), 'YYYY-MM-DD');
    v_age := (months_between(v_now, v_dob))/12;

    dbms_output.put_line('Age is: '||v_age);

  end loop;
end;

The result I get is the following:

Statement processed.
Age is: -1935.252688172043010752688172043010752692
Age is: -1940.115591397849462365591397849462365592
Age is: -1951.674731182795698924731182795698924733

I guess there’s something wrong with the year but I can’t figure it out!

Answer

The error is in this line:

v_now date :=to_date(sysdate, 'YYYY-MM-DD');

You should not call to_date on sysdate, because sysdate already has the data type date.

You should just do:

v_now date := sysdate;

Or, even leave out that variable, and use sysdate directly in your calculation.

I would also change the other date format to YYYYMMDD, since your table data has no hyphens.

Note that you can do this without PL/SQL, with a query:

select pnr,
       months_between(sysdate, to_date(substr(pnr,1,8), 'YYYYMMDD'))/12
from   carowners;