oracle 文本日期相减,日期相减等于整数的问题

最初由 jackywood 发布

[B]自己以前写的函数, 也许对楼主的问题有帮助

sql server中有同名函数

[php]

create or replace function DATEDIFF(datepart varchar2, startdate date, enddate date) return number is

Result number;

v_year   integer;

v_month  integer;

v_day    integer;

v_hour   integer;

v_minute integer;

v_second integer;

v_datepart varchar2(30);

begin

v_datepart := lower(datepart);

/*

Datepart         Abbreviations

year             yy, y

quarter          qq, q

month            mm, m

day              dd, d

week             wk, w

hour             hh, h

minute           mi, n

second           ss, s

*/

v_year := to_char(enddate,'yyyy')-to_char(startdate,'yyyy');

v_month := to_char(enddate,'mm')-to_char(startdate,'mm');

v_day := trunc(enddate) - trunc(startdate);

v_hour := to_char(enddate,'hh24')-to_char(startdate,'hh24');

v_minute := to_char(enddate,'mi')-to_char(startdate,'mi');

v_second := to_char(enddate,'ss')-to_char(startdate,'ss');

if v_datepart in ('year','yy','y') then

result := v_year;

elsif v_datepart in ('quarter','qq','q') then

result := v_year * 4 + ceil(v_month/3)-1;

elsif v_datepart in ('month','mm','m') then

result := v_year * 12 + v_month;

elsif v_datepart in ('week','wk','w') then

result := trunc(v_day/7);

elsif v_datepart in ('day','dd','d') then

result := v_day;

elsif v_datepart in ('hour','hh') then

result := v_day * 24 + v_hour;

elsif v_datepart in ('minute','mi','n') then

result := ( v_day * 24 + v_hour ) * 60 + v_minute;

elsif v_datepart in ('second','ss','s') then

result := ( ( v_day * 24 + v_hour ) * 60 + v_minute ) * 60 + v_second;

else

RAISE_APPLICATION_ERROR(-20001, ''''||datepart||''' is not a recognized dateadd option.' );

end if;

return(Result);

EXCEPTION

WHEN OTHERS THEN

RAISE ;

end DATEDIFF;

/

------

[/php] [/B]