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

From: Andrew Velichko <andrew.velichko_at_globeinteractive.com>
Date: Sun, 11 Mar 2001 14:27:40 -0500
Message-ID: <CrQq6.152317$Z2.1923226_at_nnrp1.uunet.ca>

[Quoted] 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
[Quoted] (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;
[Quoted] returns 8.3041095890410958904109589041095890411 years (though this last one does not
[Quoted] 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:27:40 CET