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

"sam" <sam_eh_at_hotmail.com> a écrit dans le 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
>
>

You can use somethig like:

v815> select months_between(to_date('03/06/2001','MM/DD/YYYY'),

  2                         to_date('11/17/1992','MM/DD/YYYY'))/12
  3          "Years",
  4          months_between(to_date('03/06/2001','MM/DD/YYYY'),
  5                         to_date('11/17/1992','MM/DD/YYYY'))
  6          "Total Months",
  7          to_date('03/06/2001','MM/DD/YYYY') -
  8          to_date('11/17/1992','MM/DD/YYYY') "Total Days",
  9          trunc(months_between(to_date('03/06/2001','MM/DD/YYYY'),
 10                               to_date('11/17/1992','MM/DD/YYYY'))/12)
 11          ||' years '||
 12          mod(trunc(months_between(to_date('03/06/2001','MM/DD/YYYY'),
 13                                   to_date('11/17/1992','MM/DD/YYYY'))),12)
 14          ||' months '||
 15          trunc(to_date('03/06/2001','MM/DD/YYYY') -
 16                add_months(to_date('11/17/1992','MM/DD/YYYY'),
 17                           months_between(to_date('03/06/2001','MM/DD/YYYY'),
 18                                          to_date('11/17/1992','MM/DD/YYYY'))))
 19          ||' days' "Year_Month_Day"

     Years Total Months Total Days Year_Month_Day

---------- ------------ ---------- ------------------------
8.30376344   99.6451613       3031 8 years 3 months 17 days

--
Have a nice day
Michel