DATE DIFF IN YEARS

Question

How to find date diff in years in 'YYYY-MM-DD' format .

I am using below two querues:

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

ORA-01861: literal does not match format string

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

I suggest to use "months_between" function because it takes leap years into account (months_between wants 2 dates as parameters):

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

26,4729751904122

of course, if you need to truncate:

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

26