Re: Calculating age of a person in PL/SQL

The problem with this is the error that gets introduced due to leap years. 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.819413_at_news1.news.adelphia.net>, "Scott Mattes" <ScottMattes_at_yahoo.com> wrote:
>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.
>
>
>"Hans Nesbø" <hans.nesboe_at_selmer.skanska.no> wrote in message
>news:9n5u8b$4nr$1_at_pippin.skanska.se...
>> Or you can:
>> select to_char(sysdate,'YYYY') - to_char(birth_date,'YYYY') from ........
>>
>> Best regards Hans
>>
>> "Randi Wølner" <randiwolner_at_hotmail.com> skrev i melding
>> news:9n4uc8$jnr$1_at_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
>> >
>> >
>>
>>
>
>
Received on Thu Sep 06 2001 - 07:58:55 CDT