Friday, October 22, 2010

Oracle Dates difference in Days

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));
--Just Retrun days as of now, igonore rest output.
 --return(years||' Years '||months||' Months '||days||' Days '||hrs||' Hours '||mints||' Minutes '||sec||' Seconds');
 return(days);
end;