sql里datetime类型的数据如何加减修改 sql中的datetime类型可以与数字加减操作吗
sql\u4e2d\u5982\u4f55\u4f7fdatetime\u7c7b\u578b\u7684\u6570\u636e\u53ea\u663e\u793a\u65e5\u671f?\u663e\u793a\u7684\u65f6\u5019\u5904\u7406\u4e00\u4e0b\uff1aConvert.ToDateTime(row["Date"]).ToString("yyy-MM-dd");
\uff081\uff09date\u662fSQL Server 2008\u65b0\u5f15\u8fdb\u7684\u6570\u636e\u7c7b\u578b\u3002\u5b83\u8868\u793a\u4e00\u4e2a\u65e5\u5b50\uff0c\u4e0d\u5305\u542b\u65f6\u95f4\u90e8\u5206\uff0c\u53ef\u4ee5\u8868\u793a\u7684\u65e5\u671f\u8303\u56f4\u4ece\u516c\u5143\u5143\u5e741\u67081\u65e5\u52309999\u5e7412\u670831\u65e5\u3002\u53ea\u9700\u89813\u4e2a\u5b57\u8282\u7684\u5b58\u50a8\u7a7a\u95f4\u3002
\uff082\uff09DateTime \u65e5\u671f\u548c\u65f6\u95f4\u90e8\u5206\uff0c\u53ef\u4ee5\u8868\u793a\u7684\u65e5\u671f\u8303\u56f4\u4ece\u516c\u51431753\u5e741\u67081\u65e500:00:00.000 \u52309999\u5e7412\u670831\u65e523:59:59.997 \uff0c\u7cbe\u786e\u52303.33\u6beb\u79d2\uff0c\u5b83\u9700\u89818\u4e2a\u5b57\u8282\u7684\u5b58\u50a8\u7a7a\u95f4\u3002
SQL\u8bed\u8a00\uff0c\u662f\u7ed3\u6784\u5316\u67e5\u8be2\u8bed\u8a00(Structured Query Language)\u7684\u7b80\u79f0\u3002SQL\u8bed\u8a00\u662f\u4e00\u79cd\u6570\u636e\u5e93\u67e5\u8be2\u548c\u7a0b\u5e8f\u8bbe\u8ba1\u8bed\u8a00\uff0c\u7528\u4e8e\u5b58\u53d6\u6570\u636e\u4ee5\u53ca\u67e5\u8be2\u3001\u66f4\u65b0\u548c\u7ba1\u7406\u5173\u7cfb\u6570\u636e\u5e93\u7cfb\u7edf\uff1b\u540c\u65f6\u4e5f\u662f\u6570\u636e\u5e93\u811a\u672c\u6587\u4ef6\u7684\u6269\u5c55\u540d\u3002
SQL\u8bed\u8a00\u662f\u9ad8\u7ea7\u7684\u975e\u8fc7\u7a0b\u5316\u7f16\u7a0b\u8bed\u8a00\uff0c\u5141\u8bb8\u7528\u6237\u5728\u9ad8\u5c42\u6570\u636e\u7ed3\u6784\u4e0a\u5de5\u4f5c\u3002\u5b83\u4e0d\u8981\u6c42\u7528\u6237\u6307\u5b9a\u5bf9\u6570\u636e\u7684\u5b58\u653e\u65b9\u6cd5\uff0c\u4e5f\u4e0d\u9700\u8981\u7528\u6237\u4e86\u89e3\u5177\u4f53\u7684\u6570\u636e\u5b58\u653e\u65b9\u5f0f\uff0c\u6240\u4ee5\u5177\u6709\u5b8c\u5168\u4e0d\u540c\u5e95\u5c42\u7ed3\u6784\u7684\u4e0d\u540c\u6570\u636e\u5e93\u7cfb\u7edf\u53ef\u4ee5\u4f7f\u7528\u76f8\u540c\u7684\u7ed3\u6784\u5316\u67e5\u8be2\u8bed\u8a00\u4f5c\u4e3a\u6570\u636e\u8f93\u5165\u4e0e\u7ba1\u7406\u7684\u63a5\u53e3\u3002SQL\u8bed\u8a00\u8bed\u53e5\u53ef\u4ee5\u5d4c\u5957\uff0c\u8fd9\u4f7f\u4ed6\u5177\u6709\u6781\u5927\u7684\u7075\u6d3b\u6027\u548c\u5f3a\u5927\u7684\u529f\u80fd\u3002
\u4e24\u4e2a\u65f6\u95f4\u5dee\u7528datediff,\u4e00\u4e2a\u65f6\u95f4\u52a0\u4e0a\u4e00\u4e2a\u6574\u6570\u7528dateadd/datepart
dateadd函数。具体用法查看sql帮助。比如加7天，dateadd(dd,7,'2015-01-01 08:00:00')
减一个月，dateadd(mm,-1,'2015-01-01 08:00:00')
日期用单引号引起来，日期字符串会被自动转换为datetime类型
比如
insert
into
表(时间列)
select
'2006-6-1
22:22:22'
就是把2006年6月1日22点22分22秒，插入到表中。
绛旓細浣犲彲浠ヨ繖鏍蜂娇鐢 SELECT from table WHERE convert(varchar(50),DATAGETTIME)LIKE '2008-9-15%'灏辨槸鍏堢敤convert鍑芥暟鎶DATETIME鏍煎紡鐨 鏁版嵁杞崲 鎴恦archar鏍煎紡锛岀劧鍚庡氨鍙互鐢╨ike浜嗐
绛旓細dateadd鍑芥暟銆傚叿浣撶敤娉曟煡鐪sql甯姪銆傛瘮濡傚姞7澶╋紝dateadd(dd,7,'2015-01-01 08:00:00')鍑忎竴涓湀锛宒ateadd(mm,-1,'2015-01-01 08:00:00')
绛旓細杩欐槸鏌ヨ涓庡綋鍓嶆椂闂村湪鍚屼竴鍛ㄥ唴鐨勬暟鎹紝sql璇彞鏄細select * from 琛 where datediff(week,鏃堕棿瀛楁,getdate())=0 涔熷彲浠ョ◢浣滀慨鏀圭敤鏉ユ煡璇笌鎸囧畾鏃ユ湡鍦ㄥ悓涓鍛ㄥ唴鐨勬暟鎹細select * from 琛 where datediff(week,鏃堕棿瀛楁,'2002-01-01')=0 鍍忎綘鐨勮繖涓〃锛屾煡璇竴娈垫椂闂...
绛旓細SqlServer閲岃竟鍙湁datetime绫诲瀷鐨瀛楁绫诲瀷锛岄【鍚嶆濅箟datetime灏辨槸鏃ユ湡鍜屾椂闂寸粍鍚堣捣鏉ョ殑绫诲瀷锛屾墍浠ヤ繚瀛樼殑鏃跺欐椂闂存槸鏃犳硶鍙栨秷鐨勶紝浣嗘槸鏄剧ず鐨勬椂鍊欏彲浠ユ妸鏃堕棿缁欏睆钄芥帀锛屼唬鐮佸涓嬶細select convert(varchar(10),getdate(),120)杩欐牱鏄剧ず鍑烘潵鐨勫氨鏄 2009-08-30 浠ヤ笂锛屽笇鏈涘浣犳湁鎵甯姪锛
绛旓細SQL鍛戒护涓璂ateTime鏍煎紡澶у叏 Code in C# :DateTime MyDate = DateTime.Now;sSQL = "Insert Into Article (Title,CreatedDate) Values (\'New Title\',\'" + MyDate.Format( "g", null ) + "\')";鏍煎紡瀛楃鍙傝冭〃锛欶ormat character Description Default return format d Short date pattern MM...
绛旓細1銆侀鍏堣幏鍙栧綋鍓嶇郴缁熸暟鎹簱鐨勬棩鏈熷拰鏃堕棿锛屽彲浠ュ埄鐢ㄥ嚱鏁癎ETDATE()锛宻elect GETDATE();濡備笅鍥炬墍绀恒2銆佽幏鍙栧綋鍓嶄笘鐣屾爣鍑嗘椂闂寸殑鏃ユ湡鏃堕棿鍊硷紝鍒╃敤GETUTCDATE()鍑芥暟锛宻elect GETUTCDATE();濡備笅鍥炬墍绀恒3銆佺劧鍚巗elect DATEADD(DAY,1,'2015-5-11');鍦ㄦ寚瀹氱殑鏃堕棿澧炲姞涓澶┿4銆佸湪鎸囧畾鐨勬椂闂村鍔犱袱骞达紝select ...
绛旓細sql server 2008 鏃ユ湡绫诲瀷鏈塂ATETIME 鍜孌ATE绫诲瀷锛屽缓璁噰鐢DATETIME绫诲瀷 鍥犱负sql server 2005鍙敮鎸丏ATETIME绫诲瀷 DATE 绫诲瀷鍊间负 '2013-01-01'DATETIME 绫诲瀷鍊间负 '2013-01-01 23:13.33.993'浣犱繚瀛樻椂鑻ュ彧鏈夋棩鏈燂紝鍊煎皢鏄剧ず涓 '2013-01-01 00:00:00:000'褰撹幏鍙栧兼椂锛岃嫢鏄疌#璇█杞寲瀛楃涓诧紝寰堢畝鍗 ...
绛旓細鐢–ONVERT()鍑芥暟闃裤傚嚱鏁拌娉曪細CONVERT(data_type(length),data_to_be_converted,style)鍋囪浣犺緭鍏ョ殑key鍊兼槸yymmdd杩欐牱鐨勬牸寮忥紝閭ｄ箞浣犲彲浠ヨ繖鏍峰啓锛歴elect from news where convert(VARCHAR(100),infotime,112)like '%"& key &"%'order by id desc 濡傛灉杈撳叆鐨刱ey鏄叾瀹冩牸寮忥紝閭ｄ箞style瑕佹敼锛岀綉涓婃湁...
绛旓細濡傛灉鎯虫煡璇2012骞11鏈11鏃ヤ箣鍚庣殑璁板綍锛屾妸'<'鍙锋敼鎴'>'鍗冲彲锛屾敞鎰忎竴瀹氳鐢ㄥ崟寮曞彿寮曡捣鏉ワ紝涓嶈兘涓嶇敤寮曞彿锛屼篃涓嶈兘鐢ㄥ弻寮曞彿锛屽彧鑳界敤鍗曞紩鍙 鎴栬呬娇鐢ㄨ浆鎹㈠嚱鏁癱ast()锛屾妸'2012-11-11'杞崲鎴愭棩鏈鍨锛屾柟娉曪細cast('2012-11-11' as datetime)銆備笂杩版煡璇㈣繕鍙互鍐欐垚 SELECT id FROM table WHERE add_user...
绛旓細鍒嗕袱绉嶆儏鍐碉紝涓绉嶆槸鏌ヨ鏃舵妸鏃跺垎绉掔瓑鍘绘帀锛屽彟涓绉嶆槸鏂板缓涓涓瓧娈碉紝鍙繚瀛樻棩鏈熴傛祴璇曟柟娉曞涓嬶細鍒涘缓琛細1 2 3 CREATE TABLE [test]([id][int]NULL,[starttime][datetime]);鎻掑叆鏁版嵁锛1 insert into test values (1,getdate());濡傛灉鏌ヨ鏃跺幓鎺夊氨寰堢畝鍗曪紝鐢ㄥ涓嬭鍙ワ細1 select ID,CONVERT(...
