sql语句怎么来对日期进行相加减？ sql语句怎么来对日期进行相加减
sql\u8bed\u53e5\u600e\u4e48\u6765\u5bf9\u65e5\u671f\u8fdb\u884c\u76f8\u52a0\u51cf\u5728sql server\u91cc\u53ef\u4ee5\u4f7f\u7528\uff1a
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') \uff1b
oracle\u4e2d\u6ca1\u6709\u5b9a\u4e49\u548csql server\u4e2d\u4e00\u6837\u7684DateAdd\u51fd\u6570,
oracle\u53ef\u4ee5\u901a\u8fc7interval 'n' year/month/day/hour/minute/second/second(p,s)
\u7684\u65b9\u5f0f\u6765\u589e\u51cf\u65f6\u95f4
\u4e0b\u9762\u662f\u81ea\u5df1\u5728oracle\u4e2d\u5199\u7684DateAdd\u51fd\u6570
\u51fd\u6570\u8c03\u7528\u57fa\u672c\u540csql server\u4e00\u6837, \u4e0d\u8fc7datepart\u90e8\u5206\u9700\u8981\u4ee5\u5b57\u7b26\u4e32\u7684\u65b9\u5f0f\u8f93\u5165, \u5373
DateAdd(d,1,to_date('2005-12-09','yyyy-mm-dd'))
\u8981\u6539\u4e3a
DateAdd('d',1,to_date('2005-12-09','yyyy-mm-dd'))
\u51fd\u6570\u5b9a\u4e49\u5982\u4e0b\u51fd\u6570\u4e2d\u7684\u6ce8\u91ca\u662fdatepart\u7684\u65b0\u8bf4\u660e, \u4e0esql server\u4e2d\u7684\u7565\u6709\u4e0d\u540c)
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;
\u5728sql server\u91cc\u53ef\u4ee5\u4f7f\u7528\uff1a
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') \uff1b
oracle\u4e2d\u6ca1\u6709\u5b9a\u4e49\u548csql server\u4e2d\u4e00\u6837\u7684DateAdd\u51fd\u6570,
oracle\u53ef\u4ee5\u901a\u8fc7interval 'n' year/month/day/hour/minute/second/second(p,s)
\u7684\u65b9\u5f0f\u6765\u589e\u51cf\u65f6\u95f4
\u4e0b\u9762\u662f\u81ea\u5df1\u5728oracle\u4e2d\u5199\u7684DateAdd\u51fd\u6570
\u51fd\u6570\u8c03\u7528\u57fa\u672c\u540csql server\u4e00\u6837, \u4e0d\u8fc7datepart\u90e8\u5206\u9700\u8981\u4ee5\u5b57\u7b26\u4e32\u7684\u65b9\u5f0f\u8f93\u5165, \u5373
DateAdd(d,1,to_date('2005-12-09','yyyy-mm-dd'))
\u8981\u6539\u4e3a
DateAdd('d',1,to_date('2005-12-09','yyyy-mm-dd'))
\u51fd\u6570\u5b9a\u4e49\u5982\u4e0b\u51fd\u6570\u4e2d\u7684\u6ce8\u91ca\u662fdatepart\u7684\u65b0\u8bf4\u660e, \u4e0esql server\u4e2d\u7684\u7565\u6709\u4e0d\u540c)
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;
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;
日期增减计算
使用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 行受影响)
select DATEDIFF(day,'2011-1-1','2011-1-8') --这是计算间隔
这个如果直接算的话，可以直接减的，和int类型的一样
直接加减就可以，日期型就是长整型的数据。
同意一楼的意见！
绛旓細鏄绠楃殑缁堟鏃ユ湡銆俥nddate 鏄繑鍥 datetime 鎴 smalldatetime 鍊兼垨鏃ユ湡鏍煎紡瀛楃涓茬殑琛ㄨ揪寮忋傝繑鍥炵被鍨 integer 娉ㄩ噴 startdate 鏄粠 enddate 鍑忓幓銆傚鏋 startdate 姣 enddate 鏅氾紝杩斿洖璐熷笺傚綋缁撴灉瓒呭嚭鏁存暟鍊艰寖鍥达紝DATEDIFF 浜х敓閿欒銆瀵逛簬姣锛屾渶澶ф暟鏄 24 澶 20 灏忔椂 31 鍒嗛挓闆 23.647 绉掋傚浜庣...
绛旓細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') the...
绛旓細锛1锛dateadd 鏂规硶锛屽綋鍓嶆椂闂村姞涓涓暟 select dateadd(hour,5,getdate()) 褰撳墠鏃堕棿鍔5灏忔椂 select dateadd(day,5,getdate()) 褰撳墠鏃堕棿鍔5澶 select dateadd(month,-1,getdate()) 褰撳墠鏃堕棿鍑1涓湀 select dateadd(year,1,getdate()) 褰撳墠鏃堕棿鍔1骞 锛2锛塪ateadd鍜宒atepart 鏂规硶锛屽綋鍓嶆椂闂...
绛旓細濡傛灉鏄鏃ユ湡鍨嬶紝鐩存帴鍔30灏辫浜嗭紝濡傛灉鏄 update time set indate=indate+30 鎴戞祴璇曡繃浜
绛旓細select username,convert(varchar(10),[datetime],120),sum(number) from [table] group by username,convert(varchar(10),[datetime],120)
绛旓細create table x(id int ,xyear varchar(20),xday varchar(20))insert into x (id,xyear ,xday ) values (1,'2015','2');insert into x (id,xyear ,xday ) values (2,'2014','12');alter table x add xdatetime varchar(20)update x set [xdatetime] = s from (...
绛旓細鏁板瓧灏辩洿鎺鐩稿姞锛岀粨鏋滄槸鍜屻傚 3+4=7 瀛楃鐩稿姞鏄粍鍚堬細濡 'aa'+'bb'='aabb'鏃ユ湡+鏃ユ湡鏄椂闂达細2涓椂闂村垎鍒笌1900骞1鏈1鏃ョ浉宸殑鍜屻傚锛氭棩鏈1+鏃ユ湡2=锛堟棩鏈1 - '1900-1-1'锛+锛堟棩鏈2 -'1900-1-1'锛+'1900-1-1'
绛旓細1銆侀鍏堝０鏄庝袱涓椂闂村彉閲忥細璧峰鏃堕棿鍜岀粨鏉熸椂闂 2銆佸皢鏃堕棿鍙橀噺鍔犲叆鍒皐here鐨勭瓫閫夋潯浠朵腑 --璧峰鏃堕棿Declare @start_date datetimeset @start_date='2018-03-28'--缁撴潫鏃堕棿Declare @end_date datetimeset @end_date='2018-04-18'declare @fengongsi varchar(50)set @fengongsi='鏃犻敗鍒嗗叕鍙'select Ding...
绛旓細鐪嬫牱瀛愬儚sqlserver锛屼綘姣忎釜鏃堕棿閮芥槸涓嶄竴鏍风殑锛屽綋鐒朵笉鑳芥眹鎬伙紝浣犳槸瑕佹寜鏃ユ眹鎬荤殑璇濓紝闇瑕瀵规棩鏈熻繘琛杞崲锛屾瘮濡 select convert(varchar(10),crtime,120) 鏃堕棿,sum(cnum) 鎬诲拰 from caiwu where ctime between '2018-09-08 08:12:05.000' and '2018-09-12 08:12:05.000' group by convert(...
绛旓細select dateadd( day, 10 ,'2016-01-01' )缁撴灉涓猴細'2016-01-11'
