在sql server里可以使用：

where start_date <=

          DateAdd(d,1,to_date('2005-12-09','yyyy-mm-dd'))

    and completion_date >=

          to_date('2005-12-09', 'yyyy-mm-dd') ；

oracle中没有定义和sql server中一样的DateAdd函数, 

oracle可以通过interval 'n' year/month/day/hour/minute/second/second(p,s)

的方式来增减时间

下面是在oracle中写的DateAdd函数

函数调用基本同sql server一样, 不过datepart部分需要以字符串的方式输入, 即

DateAdd(d,1,to_date('2005-12-09','yyyy-mm-dd'))

要改为

DateAdd('d',1,to_date('2005-12-09','yyyy-mm-dd'))



函数定义如下函数中的注释是datepart的新说明, 与sql server中的略有不同)

create or replace function DATEADD( datepart  varchar2, num number, indate date ) return date is

  Result date;

  v_sql varchar2(1000);

  v_datepart varchar2(30);

  v_ms varchar2(13);

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

     millisecond      ms

  */

  case 

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

      v_sql := 'select :1 + interval '''||num||''' year from dual';

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

      v_sql := 'select :1 + (interval ''3'' month) * '||num||' from dual';

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

      v_sql := 'select :1 + interval '''||num||''' month from dual';

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

      v_sql := 'select :1 + (interval ''7'' day) * '||num||' from dual';

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

      v_sql := 'select :1 + interval '''||num||''' day from dual';

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

      v_sql := 'select :1 + interval '''||num||''' hour from dual';

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

      v_sql := 'select :1 + interval '''||num||''' minute from dual';

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

      v_sql := 'select :1 + interval '''||num||''' second from dual';

    when v_datepart in ('millisecond','ms') then

      v_ms := to_char(num/1000,'fm999999990.000'); 

      v_sql := 'select :1 + interval '''||v_ms||''' second(9,3) from dual';

    else

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

  end case;

  

  execute immediate v_sql into Result using indate;

  

  return(Result);

  

EXCEPTION

  WHEN OTHERS THEN

    RAISE ;

    

end DATEADD;