how to find no of years

[castlerock]

Hi,
How to find the no. of years between two dates. Is there any function like date between to find the no of years between two dates

[stecal]

Search for "date arithmetic" in the SQL Reference Guide.

[tamilselvan]

 1  select sysdate, sysdate-2000,
 2  to_number(to_char(sysdate,'YYYY')) - 
     to_number(to_char((sysdate-2000),'YYYY')) diff
 3* from dual
SQL> /

SYSDATE              SYSDATE-2000               DIFF
-------------------- -------------------- ----------
15-dec-2004 13:11:23 25-jun-1999 13:11:23          5 

Is this you are looking for?

Tamil

[slimdave]

How about ...

Floor(Months_Between(date1,date2)/12)

[stecal]

Or (something easy to experiment with in scott/tiger):

select (sysdate-hiredate)/365 from emp;

This can be further refined to return the whole number (5.234 years counts as 5), or the integer part plus one (5.234 years counts as 6).