以年为单位的日期差异

Question

如何在'YYYY-MM-DD'中查找年份的日期差异格式。

我使用下面两个查询:

SELECT TRUNC(TO_NUMBER(SYSDATE - TO_DATE('1994-08-13')) / 365.25) AS AGE FROM DUAL;

ORA-01861:文字不匹配格式字符串

SELECT (TO_DATE(SYSDATE, 'YYYY-MM-DD') - TO_NUMBER('1994-08-13', 'YYYY-MM-DD')) FROM DUAL;

O/p -: -727738

Desired o/p: 26

Answer 1

我建议使用"months_between"函数，因为它考虑了闰年(months_between需要2个日期作为参数):

select months_between(sysdate, to_date('1994-08-13', 'YYYY-MM-DD'))/12 from dual;
26,4729751904122

当然，如果你需要截断

select trunc(months_between(sysdate, to_date('1994-08-13', 'YYYY-MM-DD'))/12) from dual;
26

Answer 2

只需从另一个日期减去一个日期:

sysdate - date '1994-08-13'

或

sysdate - to_date('1994-08-13', 'yyyy-mm-dd')

返回两个日期之间的天数。

所以重写你的第一个查询:

select (sysdate - date '1994-08-13')  / 365.25 as age
from dual

Answer 3

那么，提取从这些日期中减去年份，然后减去:

SQL> select extract (year from sysdate) - extract(year from date '1994-08-13') diff
2  from dual;
DIFF
----------
27
SQL>

,

SQL> select 2021 - 1994 diff from dual;
DIFF
----------
27
SQL>