与Lalit的答案非常相似，但是您可以
add_months通过按整个月的总差异进行调整来获得准确的天数，而无需假设每月有30天：
select sysdate, hiredate, trunc(months_between(sysdate,hiredate) / 12) as years, trunc(months_between(sysdate,hiredate) - (trunc(months_between(sysdate,hiredate) / 12) * 12)) as months, trunc(sysdate) - add_months(hiredate, trunc(months_between(sysdate,hiredate))) as daysfrom emp;SYSDATE HIREDATE YEARS MonTHS DAYS---------- ---------- ---------- ---------- ----------2015-10-26 1980-12-17 34 10 92015-10-26 1981-02-20 34 8 62015-10-26 1981-02-22 34 8 42015-10-26 1981-04-02 34 6 242015-10-26 1981-09-28 34 0 282015-10-26 1981-05-01 34 5 252015-10-26 1981-06-09 34 4 172015-10-26 1982-12-09 32 10 172015-10-26 1981-11-17 33 11 92015-10-26 1981-09-08 34 1 182015-10-26 1983-01-12 32 9 142015-10-26 1981-12-03 33 10 232015-10-26 1981-12-03 33 10 232015-10-26 1982-01-23 33 9 3
您可以通过反转计算来验证：
with tmp as ( select trunc(sysdate) as today, hiredate, trunc(months_between(sysdate,hiredate) / 12) as years, trunc(months_between(sysdate,hiredate) - (trunc(months_between(sysdate,hiredate) / 12) * 12)) as months, trunc(sysdate) - add_months(hiredate, trunc(months_between(sysdate,hiredate))) as days from emp)select * from tmpwhere today != add_months(hiredate, (12 * years) + months) + days;no rows selected
