oracle年龄计算函数天 月 年,如何使用Oracle获得年龄，月份和天数的年龄

小编典典

与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 days

from emp;

SYSDATE HIREDATE YEARS MONTHS DAYS

---------- ---------- ---------- ---------- ----------

2015-10-26 1980-12-17 34 10 9

2015-10-26 1981-02-20 34 8 6

2015-10-26 1981-02-22 34 8 4

2015-10-26 1981-04-02 34 6 24

2015-10-26 1981-09-28 34 0 28

2015-10-26 1981-05-01 34 5 25

2015-10-26 1981-06-09 34 4 17

2015-10-26 1982-12-09 32 10 17

2015-10-26 1981-11-17 33 11 9

2015-10-26 1981-09-08 34 1 18

2015-10-26 1983-01-12 32 9 14

2015-10-26 1981-12-03 33 10 23

2015-10-26 1981-12-03 33 10 23

2015-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 tmp

where today != add_months(hiredate, (12 * years) + months) + days;

no rows selected

2021-03-10