Re: Calculating age of a person in PL/SQL

David,
  I stand corrected, too small a data sample was used in testing. Besides, I should know better.

  Thank you.

"David Fitzjarrell" <oratune_at_msn.com> wrote in message news:32d39fb1.0109071421.202c9ccb_at_posting.google.com...
> I beg to differ:
>
> 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" <ScottMattes_at_yahoo.com> wrote in message
news:<JRPl7.1065$he4.1047294_at_news1.news.adelphia.net>...
> > Actually, they both do. Though I thought that I had tried trunc() first
and
> > 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" <oratune_at_msn.com> wrote in message
> > news:32d39fb1.0109060925.3682d747_at_posting.google.com...
> > > "Scott Mattes" <ScottMattes_at_yahoo.com> wrote in message
> > news:<iYxl7.766$he4.819413_at_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.
> > > >
> >
> > >
> > > TRUNC() provides the proper year, not ROUND().
> > >
> > > David Fitzjarrell
> > > Oracle Certified DBA
Received on Mon Sep 10 2001 - 10:43:33 CDT