Re: Calculating age of a person in PL/SQL

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 Fri Sep 07 2001 - 17:21:40 CDT