createorreplacefunction

-- -- works in roughly the same way assybase datsdiff

-- call would be eg.datediff('month',date1,date2)

-- p_what would be 'HOUR', 'DAY','MONTH' OR'QUARTER'

-- date2- date1

datediff( p_what invarchar2,

      p_d1 in date,

      p_d2 in date ) returnnumber

as

l_result number;

begin

l_result:=0;

if (upper(p_what) = 'HOUR') then

  l_result:=((p_d2-p_d1)*24);

endif;

if (upper(p_what) = 'DAY') then

  l_result:=(p_d2-p_d1);

endif;

if (upper(p_what) = 'MONTH') then

  l_result:=round(MONTHS_BETWEEN(p_d2,p_d1),0);

endif;

if (upper(p_what) = 'QUARTER') then

  l_result:=((floor(MONTHS_BETWEEN(p_d2,TRUNC(p_d2,'YEAR'))/3)+1) -(floor(MONTHS_BETWEEN(p_d1,TRUNC(p_d1,'YEAR'))/3)+1) + (((to_char(p_d2, 'yyyy')) - (to_char(p_d1, 'yyyy')))*4));

endif;

  l_result:=floor(l_result);

   return l_result;

end;