Calculating age of a person in PL/SQL
Randi Wřlner
date?
I have to create a PL/SQL function for doing it, and first I tried using
MONTHS_BETWEEN and then dividing by 12,
but the result will be wrong for the month of the persons's birthday..
I guess someone has done something like this before - and hope you'll
share..
Thanks,
Randi Wřlner
cf
select trunc(months_between('05-SEP-2001','04-SEP-1971')/12) age from dual
SQL > 30
select trunc(months_between('05-SEP-2001','06-SEP-1971')/12) age from dual
SQL > 29
"Randi Wřlner" <randi...@hotmail.com> a écrit dans le message news:
9n4uc8$jnr$1...@oslo-nntp.eunet.no...
Randi Wřlner
Thank you very very much!!
-Randi
cf <ne...@sitinfo.com> wrote in message
news:3b95ff01$0$14586$4d4e...@read.news.fr.uu.net...
Hans Nesbø
select to_char(sysdate,'YYYY') - to_char(birth_date,'YYYY') from ........
Best regards Hans
"Randi Wřlner" <randi...@hotmail.com> skrev i melding
news:9n4uc8$jnr$1...@oslo-nntp.eunet.no...
Scott Mattes
select ( to_date( '01-aug-01' ) - to_date( '25-aug-1957', 'dd-mon-yyyy' ) )
/ 365 from dual;
This gives 43.964384
select ( to_date( '26-aug-01' ) - to_date( '25-aug-1957', 'dd-mon-yyyy' ) )
/ 365 from dual;
and this gives 44.032877
Add round() around it and you have years.
"Hans Nesbø" <hans....@selmer.skanska.no> wrote in message
news:9n5u8b$4nr$1...@pippin.skanska.se...
> Or you can:
> select to_char(sysdate,'YYYY') - to_char(birth_date,'YYYY') from ........
>
> Best regards Hans
>
> "Randi Wølner" <randi...@hotmail.com> skrev i melding
> news:9n4uc8$jnr$1...@oslo-nntp.eunet.no...
> > What is the most efficient way of calculating a person's age at a
certain
> > date?
> > I have to create a PL/SQL function for doing it, and first I tried using
> > MONTHS_BETWEEN and then dividing by 12,
> > but the result will be wrong for the month of the persons's birthday..
> >
> > I guess someone has done something like this before - and hope you'll
> > share..
> >
> > Thanks,
> > Randi Wølner
> >
> >
>
>
Jeff
Rounding won't work well if you need very accurate results... like to the day.
If this were your only option, it's usually slightly better to divide by
365.25 to take leap years marginally into account.
For example, using your formula:
select ( to_date( '21-aug-01' ) - to_date( '01-sep-1957', 'dd-mon-yyyy' ) ) /
365 from dual
results in precisely 44 years, which is obviously wrong, and rounding
obviously isn't going to help here. If anything it's only going to make
matters MUCH worse, as I can stretch this back as far as sometime in February
and still get 44.
Using my formula (no rounding, please--just trunc):
select ( to_date( '01-sep-01' ) - to_date( '01-sep-1957', 'dd-mon-yyyy' ) ) /
365.25 from dual
results in precisely 44 years, which is amazingly accurate (I'm amazed,
anyway).
However, to prove my first point:
select ( to_date( '01-sep-02' ) - to_date( '01-sep-01' ) ) / 365.25 from dual
select ( to_date( '02-sep-02' ) - to_date( '01-sep-01' ) ) / 365.25 from dual
gives .999315537 and 1.00205339 repectively, which, although it is an
extremely small error that clears up in a year or two, is still not 100%
accurate.
The months_between/12 will work better simply because there are only 12 months
in a year and that will ALWAYS be so. You just need to remove the decimals by
using the trunc function. This is 100% accurate to the day... with the
tiniest exception of leap day Feb 29, which it treats the same as Feb 28 in
calculations I've tested--this is an error I think anyone can overlook. ;-)
In article <iYxl7.766$he4.8...@news1.news.adelphia.net>, "Scott Mattes"
David Fitzjarrell
TRUNC() provides the proper year, not ROUND().
David Fitzjarrell
Oracle Certified DBA
Scott Mattes
it didn't work, which is why I suggested round().
15:06:53 Test->select round(( to_date( '26-aug-01' ) - to_date(
'25-aug-1957', 'dd-mon-yyyy' ) )
15:06:56 2 / 365 ) from dual;
ROUND((TO_DATE('26-AUG-01')-TO_DATE('25-AUG-1957','DD-MON-YYYY'))/365)
----------------------------------------------------------------------
44
real: 440
15:06:56 Test->select trunc(( to_date( '26-aug-01' ) - to_date(
'25-aug-1957', 'dd-mon-yyyy' ) )
15:07:10 2 / 365 ) from dual;
TRUNC((TO_DATE('26-AUG-01')-TO_DATE('25-AUG-1957','DD-MON-YYYY'))/365)
----------------------------------------------------------------------
44
"David Fitzjarrell" <ora...@msn.com> wrote in message
news:32d39fb1.01090...@posting.google.com...
> "Scott Mattes" <Scott...@yahoo.com> wrote in message
news:<iYxl7.766$he4.8...@news1.news.adelphia.net>...
> > If you just need the years old, how about
> >
> > select ( to_date( '01-aug-01' ) - to_date( '25-aug-1957',
'dd-mon-yyyy' ) )
> > / 365 from dual;
> >
> > This gives 43.964384
> >
> > select ( to_date( '26-aug-01' ) - to_date( '25-aug-1957',
'dd-mon-yyyy' ) )
> > / 365 from dual;
> >
> > and this gives 44.032877
> >
> > Add round() around it and you have years.
> >
>
David Fitzjarrell
select ( to_date( '01-aug-01' ) - to_date( '25-aug-1957',
'dd-mon-yyyy' ) )
/ 365 from dual;
This gives 43.964384
Round() then makes this figure 44, and it should STILL be 43, since
the birthdate has not yet been reached. Trunc() correctly keeps this
at 43.
select ( to_date( '26-aug-01' ) - to_date( '25-aug-1957',
'dd-mon-yyyy' ) )
/ 365 from dual;
and this gives 44.032877
Round() correctly makes this 44, the proper age since the birthdate
HAS been reached. In this case BOTH Round() and Trunc() provide the
same answer. However, round() will NOT provide the proper year if the
fractional portion of the age is .5 or greater.
David Fitzjarrell
Oracle Certified DBA
"Scott Mattes" <Scott...@yahoo.com> wrote in message news:<JRPl7.1065$he4.1...@news1.news.adelphia.net>...
Scott Mattes
I stand corrected, too small a data sample was used in testing. Besides, I
should know better.
Thank you.