在SQL server 中DATEDIFF函數(shù),在Oracle中用下面的在oracle里建立個函數(shù)轉(zhuǎn)化下

?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)?? =?? 'MINUTE')?? then
??????????? l_result:=((p_d2-p_d1)*24*60);
??????? end?? if;????
??????? 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;

DATEDIFF( 'MONTH', '11-JUL-1975 ', '07-JUN-2004 ')