Oracle date difference to get number of years
I'd use months_between
, possibly combined with floor
:
select floor(months_between(date '2012-10-10', date '2011-10-10') /12) from dual;select floor(months_between(date '2012-10-9' , date '2011-10-10') /12) from dual;
floor
makes sure you get down-rounded years. If you want the fractional parts, you obviously want to not use floor
.
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 NUMBERAS 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;";