Returning the difference in Years ,Months,Days,Hours,Minutes with two datetimes

 Execute the Function


Create or replace function date_diff ( p_date1 DATE , p_date2 DATE)
return char
is

 Years        NUMBER;
 months       NUMBER;
 days         NUMBER;
 day_fraction NUMBER;
 hrs          NUMBER;
 mints        NUMBER;
 sec          NUMBER;

begin

 Years :=trunc( months_between( p_date2 , p_date1 ) /12 );
 months:=mod( trunc( months_between( p_date2, p_date1 ) ), 12 );
 days  :=trunc(p_date2 - add_months(p_date1,trunc(months_between(p_date2,p_date1) )));

 day_fraction:= (p_date2-p_date1)-trunc(p_date2-p_date1);

 hrs   :=trunc(day_fraction*24);
 mints :=trunc((((day_fraction)*24)-(hrs))*60);
 sec   :=trunc(mod((p_date2-p_date1)*86400,60));

 return(years||' Years '||months||' Months '||days||' Days '||hrs||' Hours '||mints||' Minutes
'||sec||' Seconds');

end;
/

Execute the Query in SQL

select date_diff(TO_DATE('01/MAR/2013 1220','dd/Mon/yyyy hh24mi'),TO_DATE('01/MAR/2013 1200','dd/Mon/yyyy hh24mi')) from dual;