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
/

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 (DOT) gov.au> wrote

"Craig & Co." <crb (AT) amsa (DOT) gov.au> a écrit dans le message de
news:421420c4$0$57505$c30e37c6 (AT) ken-reader (DOT) news.telstra.net...
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_bi rth','MM/DD/YYYY'))/12)
- add_months(to_date('&&date_of_birth','MM/DD/YYYY'),
months_between(sysdate, to_date('&&date_of_birth','MM/DD/YYYY'))))

Regards
Michel Cadot

"Michel Cadot" <micadot{at}altern{dot}org> wrote

days is wrong.

Using my birthday - 07-Apr-1975 - It comes up with 29 years 10 months and 10
days.
The Days should at least be 300+.
Hence the huge last statement in the program. I do like what you've done
with the Years and Months though
and will change my program accordingly.

Cheers
Craig.

"Craig & Co." <crb (AT) amsa (DOT) gov.au> a écrit dans le message de
news:42151b79$0$57510$c30e37c6 (AT) ken-reader (DOT) news.telstra.net...
I don't understand "300+": number of days is always between 1 and number of days in the current
month minus 1.
This is the number of days you have to add to your 29 years and 10 months to get back to your birth
day.

Regards
Michel Cadot

Michel Cadot wrote:

--
Regards,
Frank van Bortel

I do apologies. Maths was never my strong point. 300+ days is actually 29
years and 300+ days, where
you were right it should be 29 years 10 months and how many days into this
month.

Will alter code appropriately.

Craig