converting date of birth to age inSQLPLUS

In our database we keep the date of birth of members. I need to convert those dates to age. I'm not having alot of luck. We use SQLPLUS. Any help is appreciated.

try something like:


365.24 = days in a year

Try the following :

select bth_date, to_number(to_char(sysdate,'YYYY')) - to_number(to_char(bth_date,'YYYY')) +
decode(sign(to_number(to_char(sysdate,'MMDD')) - to_number(to_char(bth_date,'MMDD'))),-1,-1,0) age
from (select to_date('01/jan/1994','dd/mon/yyyy') bth_date from dual
union select to_date('01/jul/1994','dd/mon/yyyy') bth_date from dual
union select to_date('22/jul/1994','dd/mon/yyyy') bth_date from dual
union select to_date('23/jul/1994','dd/mon/yyyy') bth_date from dual
union select to_date('24/jul/1994','dd/mon/yyyy') bth_date from dual
union select to_date('31/dec/1994','dd/mon/yyyy') bth_date from dual
union select to_date('01/jan/2004','dd/mon/yyyy') bth_date from dual
union select to_date('01/jan/2005','dd/mon/yyyy') bth_date from dual
)

Something like Jim's is best, however, I think most people only measure their age from their last birthday, (rather than rounding):

select trunc((sysdate - dob)/365.24) from mytable;

you could use gamyers version without all the conversions:

select to_char(sysdate,'YYYY') - to_char(bth_date,'YYYY') from mytable;

(the "-" will make SQL automatically convert to number). But, again, everyone will get their birthday on January 1st.

It depends how precise you need to be.
The 'trunc' solution can give some dodgy results when run on a person's birthday.
For example, if the calculation is run on '31-jan-2004' and a person's birthdate was '31-jan-2001', then the trunc version gives an age of 2, whereas mine gives an age of 3.

Bet you the child concerned will be loudly telling you that "I'm three today" though.

Because the ".24" doesn't reflect that we have an extra day every four years, rather than a quarter day every year, I think it will underestimate the age between 1% and 2% of the time. You could play with the script below to work out the precise percentage....

create table x (birth_date date , current_date date, first_age_calc number, second_age_cal number)

declare
v_start_date date := to_date('01-jan-2000','dd-mon-yyyy');
v_end_date date := to_date('31-dec-2004','dd-mon-yyyy');
begin
while v_start_date <= v_end_date loop
if to_number(to_char(v_start_date,'DD')) = 1
or v_start_date = last_day(v_start_date) then
insert into x (birth_date) values (v_start_date);
end if;
v_start_date := v_start_date + 1;
end loop;
end;

insert into x (birth_date, current_date)
select a.birth_date, b.birth_date
from x a, x b
where a.birth_date <= b.birth_date

delete from x
where current_date is null

update x set first_age_calc = trunc((current_date - birth_date)/365.24),
second_age_cal =
to_number(to_char(current_date,'YYYY')) - to_number(to_char(birth_date,'YYYY')) +
decode(sign(to_number(to_char(current_date,'MMDD')) - to_number(to_char(birth_date,'MMDD'))),-1,-1,0)


delete from x where first_age_calc = second_age_cal
(7119 rows deleted, leaving 141)

-- Not making a mountain of a molehill....
SELECT
TO_CHAR(SYSDATE,'YYYY') - TO_CHAR(:bd,'YYYY') -
CASE WHEN TO_CHAR(SYSDATE,'MMDD') < TO_CHAR(:BD,'MMDD') THEN 1 ELSE 0 END
FROM DUAL

EDIT: yipes - just realized this was identical to gamyers solution. Nevermind!