[oracle] Oracle date difference to get number of years

Is there a way to calculate the number of years between dates. Not sure how to do this while accounting for leap and what not. Is it possible to do an IF statement maybe in the SELECT?

Thanks

I had to implement a year diff function which works similarly to sybase datediff. In that case the real year difference is counted, not the rounded day difference. So if there are two dates separated by one day, the year difference can be 1 (see select datediff(year, '20141231', '20150101')).

If the year diff has to be counted this way then use:

EXTRACT(YEAR FROM date_to) - EXTRACT(YEAR FROM date_from)

Just for the log the (almost) complete datediff function:

CREATE OR REPLACE FUNCTION datediff (datepart IN VARCHAR2, date_from IN DATE, date_to IN DATE)
RETURN NUMBER
AS
  diff NUMBER;
BEGIN
  diff :=  CASE datepart
    WHEN 'day'   THEN TRUNC(date_to,'DD') - TRUNC(date_from, 'DD')
    WHEN 'week'  THEN (TRUNC(date_to,'DAY') - TRUNC(date_from, 'DAY')) / 7
    WHEN 'month' THEN MONTHS_BETWEEN(TRUNC(date_to, 'MONTH'), TRUNC(date_from, 'MONTH'))
    WHEN 'year'  THEN EXTRACT(YEAR FROM date_to) - EXTRACT(YEAR FROM date_from)
  END;
  RETURN diff;
END;";

If you just want the difference in years, there's:

SELECT EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2) FROM mytable

Or do you want fractional years as well?

SELECT (date1 - date2) / 365.242199 FROM mytable

365.242199 is 1 year in days, according to Google.

For Oracle SQL Developer I was able to calculate the difference in years using the below line of SQL. This was to get Years that were within 0 to 10 years difference. You can do a case like shown in some of the other responses to handle your ifs as well. Happy Coding!

TRUNC((MONTHS_BETWEEN(<DATE_ONE>, <DATE_TWO>) * 31) / 365) > 0 and TRUNC((MONTHS_BETWEEN(<DATE_ONE>, <DATE_TWO>) * 31) / 365) < 10

Need to find difference in year, if leap year the a year is of 366 days.

I dont work in oracle much, please make this better. Here is how I did:

SELECT CASE
          WHEN    ( (fromisleapyear = 'Y') AND (frommonth < 3))
               OR ( (toisleapyear = 'Y') AND (tomonth > 2)) THEN
             datedif / 366
          ELSE
             datedif / 365
       END
          yeardifference
  FROM (SELECT datedif,
               frommonth,
               tomonth,
               CASE
                  WHEN (       (MOD (fromyear, 4) = 0)
                           AND (MOD (fromyear, 100) <> 0)
                        OR (MOD (fromyear, 400) = 0)) THEN
                     'Y'
               END
                  fromisleapyear,
               CASE
                  WHEN (   (MOD (toyear, 4) = 0) AND (MOD (toyear, 100) <> 0)
                        OR (MOD (toyear, 400) = 0)) THEN
                     'Y'
               END
                  toisleapyear
          FROM (SELECT (:todate - :fromdate) AS datedif,
                       TO_CHAR (:fromdate, 'YYYY') AS fromyear,
                       TO_CHAR (:fromdate, 'MM') AS frommonth,
                       TO_CHAR (:todate, 'YYYY') AS toyear,
                       TO_CHAR (:todate, 'MM') AS tomonth
                  FROM DUAL))