create or replace function
-- -- works in roughly the same way as sybase datsdiff
-- call would be eg. datediff('month',date1,date2)
-- p_what would be 'HOUR', 'DAY','MONTH' OR 'QUARTER'
datediff( p_what in varchar2,
p_d1 in date,
p_d2 in date ) return number
as
l_result number;
begin
l_result:=null;
if (upper(p_what) = 'HOUR') then
l_result:=((p_d2-p_d1)*24);
end if;
if (upper(p_what) = 'DAY') then
l_result:=(p_d2-p_d1);
end if;
if (upper(p_what) = 'MONTH') then
l_result:=round(MONTHS_BETWEEN(p_d2,p_d1),0);
end if;
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));
end if;
l_result:=floor(l_result);
return l_result;
end;
/
SQL> SELECT DATEDIFF('MONTH','11-JUL-1975','07-JUN-2004') FROM DUAL;