FIND YEAR, MONTHS, DAY, HOUR, MINUTE AND SECOUND BETWEEN TWO DATE USING FUNCTION.

SQL> CREATE OR REPLACE function  FUN_YEAR_MONTHS_DAY( p_date1 DATE , p_date2 DATE, p_type VARCHAR2)
  2  return char
  3  is
  4
  5   Years        NUMBER;
  6   months       NUMBER;
  7   days         NUMBER;
  8   day_fraction NUMBER;
  9   hrs          NUMBER;
 10   mints        NUMBER;
 11   sec          NUMBER;
 12
 13  begin
 14
 15   Years :=trunc( months_between( p_date2 , p_date1 ) /12 );
 16   months:=mod( trunc( months_between( p_date2, p_date1 ) ), 12 );
 17   days  :=trunc(p_date2 - add_months(p_date1,trunc(months_between(p_date2,p_date1) )));
 18
 19   day_fraction:= (p_date2-p_date1)-trunc(p_date2-p_date1);
 20
 21   hrs   :=trunc(day_fraction*24);
 22   mints :=trunc((((day_fraction)*24)-(hrs))*60);
 23   sec   :=trunc(mod((p_date2-p_date1)*86400,60));
 24   If p_type='YY' Then
 25      return(years||' Years ');
 26   elsif p_type='MM' Then
 27      return(months||' Months ');
 28   elsif p_type='DD' Then
 29      return(days||' Days ');
 30   elsif p_type='YMD' Then
 31      return(years||' Years '||months||' Months '||days||' Days ');
 32   elsif p_type='ALL' Then
 33   return(years||' Years '||months||' Months '||days||' Days '||hrs||' Hours '||mints||' Minutes '||sec||' Seconds');
 34  End If;
 35
 36  end;
 37  /

Function created.

SQL> SELECT FUN_YEAR_MONTHS_DAY('12-JAN-1990',SYSDATE,'ALL') FROM DUAL;

FUN_YEAR_MONTHS_DAY('12-JAN-1990',SYSDATE,'ALL')
--------------------------------------------------------------------------------
23 Years 4 Months 5 Days 17 Hours 51 Minutes 55 Seconds

SQL>