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;
转载于:https://blog.51cto.com/agilitygod/1419899