How to print decimal number from stored string date (PL/SQL)

Question

Trying to solve one assignment where I have a table consisting of owners of cars. I am supposed to write an anonymous PLSQL statement where I should print first name, last name and how old the owners are, like if their dob is '19801109' it should convert that to their age in a number, with one decimal, like '34,4 years old'. How to do that? (very new to this as you can imagine). Here is my code, without any function for the conversion/calculation:

declare
cursor c_owners is select initcap(fname), initcap(lname), dob
                from car_owner;
v_dob car_owner.pnr%type;
v_fnamn car_owner.fname%type;
v_enamn car_owner.lname%type;
begin 
  if not c_owners%isopen then
  open c_owner;
  end if;
     loop
     fetch c_owner
     into v_fname, v_lname, v_dob;
     exit when c_owners%notfound;
     dbms_output.put_line(v_fname||', '||v_lname||', '||v_dob||'year.');
     end loop;

close c_owners;
end;
/

Would be awesome if someone could help. Been stuck with this for some time now. I know I could use better ways of writing to get less code but trying to learn the basics first :) The result should look like:

John, Johnson, 34,4 years old.

When I asked a friend he had done like this:

begin 
for rec in select fname, lname, round (months_between(sysdate,  to_Date('19'|| 
SUBSTR(dob,0), INSTR(dob, '-')-1)'YYMMDD')) as age 
from car_owner; 
loop dbms_output.put_line(initcat(rec.fname) || ', '|| initcap(rec.lname) ||' , '|| rec.age||' age'; 
end loop; 
end;
/

But that did not work (annoying right parentheses error no matter how I did), and I did not understand it fully, but maybe it helps you see how it should look? its about ten rows in the table and the output should print each ones age based on their dob.

Lookup Datediff: https://msdn.microsoft.com/en-us/library/ms189794.aspx — Christian Barron, May 21 '15 at 13:32
I missed the pl/sql tag. I also thought Oracle did but maybe I was misled by this: http://docs.oracle.com/goldengate/1212/gg-winux/GWURF/column_conversion_functions011.htm#GWURF780 — Christian Barron, May 21 '15 at 14:01
sorry but how did you get that 34.4? the closest I am getting is 34.53 — Jeremy C., May 21 '15 at 14:05
@ChristianBarron: in Oracle (or standard SQL in general) you just write `date_one - date_two` or `some_date - interval '5' day` - there is no need for such a function. — , May 21 '15 at 14:05
Im a swede so we write dates in different format, but in this case I just estimated roughly, maybe way wrong anyway :) Still have issues with this though...I updated the quetion with more info above if anyone has some ideas — Marty, May 21 '15 at 14:36

Gary_W · Answer 1 · 2015-05-21T13:50:26.563

0

You just need some math on the birthdate. Convert it to a date, subtract it from the current date and divide by 365 get years. Then format to 1 decimal with to_char():

SQL> with tbl(fname, lname, DOB) as
  2  ( select 'John', 'Johnson', '19081109' from dual
  3  )
  4  select fname, lname, to_char((sysdate - to_date('19801109','yyyymmdd'))/365, '99.9') age from tbl;

FNAM LNAME   AGE
---- ------- -----
John Johnson  34.6

SQL>

So for your example:

select initcap(fname), initcap(lname), to_char((sysdate - to_date(dob,'yyyymmdd'))/365, '99.9') || ' years old' age
                from car_owner;

edited May 21 '15 at 13:50

answered May 21 '15 at 13:46

Gary_W

9,933
1
22
40

Some years have more than 365 days in them. – Wai Ha Lee May 21 '15 at 13:47
2

try months_between(date v_dob , sysdate) /12 – Jeremy C. May 21 '15 at 13:49
Good point to bring up leap years, etc. Depends on how accurate the OP needs to be? I was suspecting a rough estimate. This is a good example of why one must be very specific in defining requirements, or making sure you are getting specific requirements! Less than specific may be interpreted incorrectly by your developers. – Gary_W May 21 '15 at 13:54

score 0 · Answer 2 · edited May 23 '17 at 10:26

There's a nice answer about getting a precise age in C# here: Age in years with decimal precision given a datetime

Translated to PL/SQL:

declare
  v_dob varchar(8) := '19801109';
  v_dob_as_date date := to_date(v_dob,'yyyymmdd');
  v_current_date date := sysdate;
  v_years integer;
  v_last_birthday date;
  v_next_birthday date;
begin
    v_years := extract(year from v_current_date) - extract(year from v_dob_as_date);
    v_last_birthday := ADD_MONTHS(v_dob_as_date, 12 * v_years);
    if v_last_birthday > v_current_date then
      v_years := v_years -1;
      v_last_birthday := ADD_MONTHS(v_dob_as_date, 12 * v_years);
    end if;
    v_next_birthday := ADD_MONTHS(v_last_birthday, 12);
    dbms_output.put_line('Years old (whole number): ' || v_years);
    dbms_output.put_line('Last birthday: ' ||  v_last_birthday);
    dbms_output.put_line('Next birthday: ' ||  v_next_birthday);
    dbms_output.put_line('Days between last and next birthdays: ' ||  (v_next_birthday - v_last_birthday));
    dbms_output.put_line('Days since last birthday: ' ||  (v_current_date - v_last_birthday));
    dbms_output.put_line('Exact age: ' || round(v_years + ( (v_current_date - v_last_birthday) / (v_next_birthday - v_last_birthday)),1));
end;

The result (with SYSDATE being 21-MAY-15 10:45:11 AM) is 34.5. You get the same thing with the much simpler procedure:

declare
  v_dob varchar(8) := '19801109';
  v_dob_as_date date := to_date(v_dob,'yyyymmdd');
begin
  dbms_output.put_line('Age: ' || round(months_between(sysdate,to_date('19801109','yyyymmdd'))/12,1));
end;

If you round to 5 decimal places, the results vary. The first method produces 34.52999, and the second produces 34.53346.

How to print decimal number from stored string date (PL/SQL)

2 Answers2

Linked