Re: How to calculate the period (years,months & days) between 2 date fields?

Hi Sam!

Sorry, I made mistake:
select
(to_date('03/06/2001','mm/dd/yyyy')-to_date('11/17/1992','mm/dd/yyyy'))/31 from dual;
returns number of months but roughly and I think that it is better to user months_between function:
select
months_between(to_date('03/06/2001','mm/dd/yyyy'),to_date('11/17/1992','mm/d d/yyyy')) from dual;
which returns 99.6451612903225806451612903225806451613 months.

Andrew.

"Andrew Velichko" <andrew.velichko_at_globeinteractive.com> wrote in message news:CrQq6.152317$Z2.1923226_at_nnrp1.uunet.ca...
> Hi Sam!
>
> When Oracle subrtacts one date from another the result will be in days.
> So
> select
 to_date('03/06/2001','mm/dd/yyyy')-to_date('11/17/1992','mm/dd/yyyy')
> from dual;
> returns 3031 days,
> select
> (to_date('03/06/2001','mm/dd/yyyy')-to_date('11/17/1992','mm/dd/yyyy'))/12
> from dual;
> returns 252.583333333333333333333333333333333333 months, and
> select
>
 (to_date('03/06/2001','mm/dd/yyyy')-to_date('11/17/1992','mm/dd/yyyy'))/365
> from dual;
> returns 8.3041095890410958904109589041095890411 years (though this last
 one
> does not
> account for leap years).
>
> Andrew Velichko
> Brainbench MVP for Oracle Developer 2000
> http://www.brainbench.com
> --------------------------------------------------------------
>
> "sam" <sam_eh_at_hotmail.com> wrote in message
> news:3AAA11E4.E4DD4A42_at_hotmail.com...
> > How to calculate the period (years,months & days) between 2 date fields?
> >
> > i want to calculate the total period of service for an employee.
> > for example :
> > the Emp.Appointment Date is 11/17/1992
> > the Emp.Termination Date is 03/06/2001
> > I want to know exactly : how many years, how many months & how many days
> > between these 2 dates.
> > i couldn't find any function to do that ! any suggestion?
> > Thanks
> >
> >
>
>
Received on Sun Mar 11 2001 - 20:35:12 CET