在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;
发表评论