select - Oracle date difference to get number of years

Question

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

Answer 1

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.