Cursor for loop - substr(char) to date, subtracting sysdate - PLSQL Oracle

Question

Maybe someone can help me.. I have the cursor for loop here. I want to print out the first name and the age (in decimals, ex. 55,6 years). I know that the WHERE part of my code is not correct. I'm trying to take the first 6 char(year and month) with substr() function from a varchar2(12) string. Then I want to change it to a date and subtract from the sysdate. Maybe I'm wrong. Feeling confused :(

CREATE TABLE client(
pers_nr VARCHAR2(12) PRIMARY KEY,
fname VARCHAR2(20);

INSERT INTO client VALUES('19490321-789','Anna');


declare 
cursor c_info_client is select fname, substr(pers_nr, 1, 6)
                        from client
           --wrong!--   where substr(pnr, 1, 6) : = to_date(YYYY-MM) - sysdate;
begin
for rec in c_info_client loop
dbms_output.put_line(initcap(rec.fname) ||', ' || rec.pers_pnr ||' years');
end loop;
end;

The answer should look like (one of many rows):

Anna, 34,7 years

Answer 1

You don't appear to want a where clause at all; you want to convert the first six characters of the pers_nr to a date, and see how many years are between that and today.

You can calculate the age with:

trunc(months_between(sysdate, to_date(substr(pers_nr, 1, 6), 'YYYYMM'))/12, 1)

• substr(pers_nr, 1, 6) gives you the six characters => 19490321
• to_date(substr(pers_nr, 1, 6), 'YYYYMM') turns that into a date => 1949-03-21
• months_between(sysdate, ...) gives you the number of months => 839.4
• months_between(sysdate, ...)/12 gives you the number of years=> 72.196
• trunc(..., 1) truncates that to one decimal place => 72.1

So your PL/SQL block would be:

declare 
  cursor c_info_client is
    select fname,
      trunc(months_between(sysdate, to_date(substr(pers_nr, 1, 6), 'YYYYMM'))/12, 1) as age
    from client;
begin
  for rec in c_info_client loop
    dbms_output.put_line(initcap(rec.fname) ||', ' || rec.age ||' years');
  end loop;
end;
/

which gives:

Anna, 72.1 years

db<>fiddle with a cursor, and a plain query to show the steps.

You could use trunc(sysdate) to take the time from midnight this morning, but it won't make much difference here.

By default the age will be shown with the decimal separator from your session NLS_NUMERIC_CHARACTERS setting. If you want all users to always see the same separate than you can use to_char() with a suitable format mask.