sql语句怎么来对日期进行相加减？

Answer 1

在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;

Answer 2

日期增减计算

使用DATEADD 函数

1> SELECT '现在' AS [日期], GETDATE() AS [数据]
2> UNION ALL
3> SELECT '下一秒' AS [日期], DATEADD(ss, 1, GETDATE() ) AS [数据]
4> UNION ALL
5> SELECT '下一分' AS [日期], DATEADD(mi, 1, GETDATE() ) AS [数据]
6> UNION ALL
7> SELECT '下一小时' AS [日期], DATEADD(hh, 1, GETDATE() ) AS [数据]
8> UNION ALL
9> SELECT '明天' AS [日期], DATEADD(dd, 1, GETDATE() ) AS [数据]
10> UNION ALL
11> SELECT '下周' AS [日期], DATEADD(ww, 1, GETDATE() ) AS [数据]
12> UNION ALL
13> SELECT '下月' AS [日期], DATEADD(mm, 1, GETDATE() ) AS [数据]
14> UNION ALL
15> SELECT '下季度' AS [日期], DATEADD(qq, 1, GETDATE() ) AS [数据]
16> UNION ALL
17> SELECT '明年' AS [日期], DATEADD(yy, 1, GETDATE() ) AS [数据]
18> go
日期 数据
-------- -----------------------
现在 2011-02-11 21:27:15.750
下一秒 2011-02-11 21:27:16.750
下一分 2011-02-11 21:28:15.750
下一小时 2011-02-11 22:27:15.750
明天 2011-02-12 21:27:15.750
下周 2011-02-18 21:27:15.750
下月 2011-03-11 21:27:15.750
下季度 2011-05-11 21:27:15.750
明年 2012-02-11 21:27:15.750

(9 行受影响)

两个日期之间的差值

1> SELECT '年' AS '日期部分', DATEDIFF(yy, '2012-12-21', GETDATE()) AS [数值]
2> UNION ALL
3> SELECT '季度' AS '日期部分', DATEDIFF(qq, '2012-12-21', GETDATE()) AS [数值]
4> UNION ALL
5> SELECT '月' AS '日期部分', DATEDIFF(mm, '2012-12-21', GETDATE()) AS [数值]
6> UNION ALL
7> SELECT '日' AS '日期部分', DATEDIFF(dd, '2012-12-21', GETDATE()) AS [数值]
8> UNION ALL
9> SELECT '周' AS '日期部分', DATEDIFF(ww, '2012-12-21', GETDATE()) AS [数值]
10> UNION ALL
11> SELECT '时' AS '日期部分', DATEDIFF(hh, '2012-12-21', GETDATE()) AS [数值]
12> UNION ALL
13> SELECT '分' AS '日期部分', DATEDIFF(mi, '2012-12-21', GETDATE()) AS [数值]
14> UNION ALL
15> SELECT '秒' AS '日期部分', DATEDIFF(ss, '2012-12-21', GETDATE()) AS [数值]
16> go
日期部分 数值
---- -----------
年 -1
季度 -7
月 -22
日 -679
周 -97
时 -16275
分 -976470
秒 -58588175

(8 行受影响)

参考资料：http://hi.baidu.com/wangzhiqing999/blog/item/4b75aa2dbe8f23ed99250a99.html

Answer 3

select DATEDIFF(day,'2011-1-1','2011-1-8') --这是计算间隔

这个如果直接算的话，可以直接减的，和int类型的一样

Answer 4

直接加减就可以，日期型就是长整型的数据。

Answer 5

同意一楼的意见！