SCRIPT: Get age of person in Years, Months, Days.

From: Craig & Co. <crb_at_amsa.gov.au>
Date: Thu, 17 Feb 2005 15:42:40 +1100
Message-ID: <421420c4$0$57505$c30e37c6@ken-reader.news.telstra.net>

Hi,

I have written a script that will get the age of a person in Years, Months and Days, based
on the sysdate and date_of_birth in a table.

Enjoy - If anyone can see an where to improve code, let me know too.

The first part I did pinch from another lister: Kimmy Chan - posted 27-May-1998.

Cheers
Craig.

select id_number,

        given_name,
        family_name,
        date_of_birth,
        /* get estimated age first */
        to_char(sysdate,'yyyy')-to_char(date_of_birth,'yyyy') -


            /* adjust the age according to the month and day */

            decode(sign(to_char(sysdate,'mm')-
            to_char(date_of_birth,'mm')),
                -1,1, /* earlier month */
                +1,0, /* later month */


            /* same month, then determine by the day part */

                0,decode(sign(to_char(sysdate,'dd')-
                to_char(date_of_birth,'dd')),
                    -1,1, /* earlier day */
                    0 /* later or same day */
        )) "Years",
        decode(abs(to_char(date_of_birth,'mm') - to_char(sysdate,'mm') -
12),12,0,
        abs(to_char(date_of_birth,'mm') - to_char(sysdate,'mm') - 12))
"Months",
        decode(round(sysdate -
to_date(to_char(to_char(date_of_birth,'DD/MM')||'/'||
        to_number(to_char(sysdate,'YYYY') - 1)),'DD/MM/YYYY'),0),
        decode (mod(to_char(sysdate,'yyyy'),4),0,366,367),0,
        round(sysdate -
to_date(to_char(to_char(date_of_birth,'DD/MM')||'/'||to_number(
        to_char(sysdate,'YYYY') - 1)),'DD/MM/YYYY'),0)) "Days"

from emp_det
order by date_of_birth, family_name
/ Received on Wed Feb 16 2005 - 22:42:40 CST