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

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Thu, 17 Feb 2005 06:34:31 +0100
Message-ID: <42142cbb$0$10013$626a14ce@news.free.fr>

"Craig & Co." <crb_at_amsa.gov.au> a écrit dans le message de 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
| /
|
|

Why don't use Oracle built-in functions?

For date_of_date enter in DD/MM/YYYY format:

select trunc(months_between(sysdate,to_date('&&date_of_birth','MM/DD/YYYY'))/12)

       || ' years ' ||
       mod(trunc(months_between(sysdate, to_date('&&date_of_birth','MM/DD/YYYY'))),12)
       || ' months ' ||
       trunc(sysdate
             - add_months(to_date('&&date_of_birth','MM/DD/YYYY'),
                          months_between(sysdate, to_date('&&date_of_birth','MM/DD/YYYY'))))
       || ' days' "Year_Month_Day"

from dual;

Regards
Michel Cadot Received on Wed Feb 16 2005 - 23:34:31 CST