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

From: Craig & Co. <crb_at_amsa.gov.au>
Date: Thu, 17 Feb 2005 16:03:46 +1100
Message-ID: <421425ab$0$57513$c30e37c6@ken-reader.news.telstra.net>

Made it a little more complicated by allowing users to enter any date. col date_of_birth format a14
select '&&date_of_birth' "Date_Of_Birth",

          /* get estimated age first */

to_char(sysdate,'yyyy')-to_char(to_date('&date_of_birth'),'yyyy') -

            /* adjust the age according to the month and day */
            decode(sign(to_char(sysdate,'mm')-
                to_char(to_date('&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(to_date('&date_of_birth'),'dd')),
                        -1,1, /* earlier day */
                        0 /* later or same day */
            )) "Years",
            decode(abs(to_char(to_date('&date_of_birth'),'mm') -
            to_char(sysdate,'mm') -
12),12,0,abs(to_char(to_date('&date_of_birth'),'mm') -
            to_char(sysdate,'mm') - 12)) "Months",
            decode(round(sysdate - to_date(to_char(to_char(

to_date('&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(
                        to_date('&date_of_birth'),'DD/MM')||'/'||to_number(
                        to_char(sysdate,'YYYY') - 1)),'DD/MM/YYYY'),0)) - 1

"Days"
from dual
/
undefine date_of_birth

Cheers
Craig

"Craig & Co." <crb_at_amsa.gov.au> wrote in message news:421420c4$0$57505$c30e37c6_at_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 - 23:03:46 CST