calculate age of person by DOB vs. system date

[cuetzpalin]

Hi,

How would I calculate a person's age by using the DOB vs. the current date?

The format of the DOB is: YYYYMMDD

What would be the easiest way of doing this?

Please advise.

Thank you!

- Eli

 

[carp]

Depending on the precision you are looking for, the following will get you pretty close:

SELECT MONTHS_BETWEEN(SYSDATE,TO_DATE('19700615','YYYYMMDD'))/12 AS Years 
FROM dual;

     YEARS
----------
39.8747778

 

[SantaMufasa]

And if you want the age in Years, plus Months, plus Days, plus Hours, plus Minutes, plus Seconds, you can use this little function:

create or replace function ager (beg_date date) return varchar2 is
	total_age                      number;
        remainder                      number;
        years                          number;
        months                         number;
        days                           number;
        hours                          number;
        minutes                        number;
        seconds                        number;
        year_txt                       varchar2(20);
        month_txt                      varchar2(20);
        day_txt                        varchar2(20);
        hour_txt                       varchar2(20);
        minute_txt                     varchar2(20);
        second_txt                     varchar2(20);
        beg_plus_yrs                   date;
        beg_plus_yrs_mos               date;
        beg_plus_yrs_mos_dys           date;
        beg_plus_yrs_mos_dys_hrs       date;
        beg_plus_yrs_mos_dys_hrs_mins  date;

    begin
        total_age := months_between(sysdate,beg_date);
        years     := trunc(total_age/12);
        beg_plus_yrs := add_months(beg_date,years*12);
        months    := trunc(months_between(sysdate,beg_plus_yrs));
        beg_plus_yrs_mos := add_months(beg_plus_yrs,months);
        days      := trunc(sysdate-beg_plus_yrs_mos);
        beg_plus_yrs_mos_dys := beg_plus_yrs_mos+days;
        hours     := trunc((sysdate-beg_plus_yrs_mos_dys)*24);
        beg_plus_yrs_mos_dys_hrs := beg_plus_yrs_mos_dys + (hours/24);
        minutes   := trunc((sysdate-beg_plus_yrs_mos_dys_hrs)*24*60);
        beg_plus_yrs_mos_dys_hrs_mins := beg_plus_yrs_mos_dys_hrs+(minutes/24/60);
        seconds   := trunc((sysdate-beg_plus_yrs_mos_dys_hrs_mins)*24*60*60);
        if years = 1 then year_txt := ' year, '; else year_txt := ' years, '; end if;
        if months = 1 then month_txt := ' month, '; else month_txt := ' months, '; end if;
        if days = 1 then day_txt := ' day, '; else day_txt := ' days, '; end if;
        if hours = 1 then hour_txt := ' hour, '; else hour_txt := ' hours, '; end if;
        if minutes = 1 then minute_txt := ' minute, '; else minute_txt := ' minutes, '; end if;
        if seconds = 1 then second_txt := ' second. '; else second_txt := ' seconds.'; end if;
        return years||year_txt||months||month_txt||days||day_txt||hours||hour_txt
          ||minutes||minute_txt||seconds||second_txt;
    end;
/

Function created.

Then you can use the function as you would any built-in DATE function:

SQL> select ager(to_date('1952-06-22 06:35:00','yyyy-mm-dd hh24:mi:ss')) Some_old_guys_age from dual;

SOME_OLD_GUYS_AGE
-------------------------------------------------------------
57 years, 10 months, 8 days, 7 hours, 54 minutes, 14 seconds.

1 row selected.

select ager(to_date('2009-03-29 13:33:00','yyyy-mm-dd hh24:mi:ss')) A_NEWBORNS_AGE from dual;

A_NEWBORNS_AGE
-----------------------------------------------------
1 year, 1 month, 1 day, 1 hour, 1 minute, 1 second.

1 row selected.

Enjoy.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[citrustech]

Just to add to carp's post, if only because I got caught out during a demo where I used round rather than floor:

SQL> SELECT floor(MONTHS_BETWEEN(SYSDATE,TO_DATE('19700615','YYYYMMDD'))/12) AS Years from dual;
YEARS
----------
39

SQL>

Dave

http://www.citrustechnology.com