关于DB2的日期计算的SQL语句,大神速进 db2中在一个日期型加几个月的运算
db2 \u5224\u65ad\u4e00\u4e2a\u65e5\u671f\u5728\u7ed9\u5b9a\u65f6\u95f4\u4e2d\u4f60\u8fd9\u8bed\u53e5\u6709\u95ee\u9898
\u5e94\u8be5\u8fd9\u6837\u67e5
select b.char_date
from b,c
where b.char_date between c.exp_date and c.imp_date
\u800c\u4e14c.exp_date \uff0cc.imp_date\u5b57\u6bb5\u8fd8\u8981\u786e\u5b9a\u4e00\u4e2a\u65e5\u671f\u503c\u624d\u6765\u505a\u8303\u56f4
\u76f4\u63a5\u5728\u65e5\u671f\u540e\u52a0\u4e0a\u201c+ n months\u201d\uff0c\u5982\uff1a
\u4eca\u5929\u662f2012\u5e743\u670828\u53f7\uff0c\u8ba1\u7b97\u4e24\u4e2a\u6708\u4e4b\u540e\u7684\u65e5\u671f
select current date + 2 months from sysibm.sysdummy1
DATE(LEFT('201202', 4) || '-'||RIGHT('201202', 2)||'-1') + 1 MONTH
from sysibm/sysdummy1
DATE(LEFT('201202', 4) || '-'||RIGHT('201202', 2)||'-1') - 1 MONTH这样拿到的是日期类型,需要cast as char,然后截取就可以了
db2处理日期很垃圾的
DB2语句不太会,如果是Oracle
1、转换为日期 to_date('201202','yyyymm')
2、求上十一个月 add_months(to_date('201202','yyyymm'),-11)
3、转换回字符 to_char(add_months(to_date('201202','yyyymm'),1),'yyyymm')
即可得到201102
如果DB2也有以上功能,请按以上思路试一下吧!
201202获得201201和201102
你是想获取去年的还是今年的
绛旓細DATE(LEFT('201202', 4) || '-'||RIGHT('201202', 2)||'-1') - 1 MONTH杩欐牱鎷垮埌鐨勬槸鏃ユ湡绫诲瀷锛岄渶瑕乧ast as char,鐒跺悗鎴彇灏卞彲浠ヤ簡 db2澶勭悊鏃ユ湡寰堝瀮鍦剧殑
绛旓細鑾峰彇DB2鏁版嵁搴撶殑褰撳墠绯荤粺鏃堕棿锛屽彲浠ラ氳繃浣跨敤鍐呯疆鐨勬棩鏈熷嚱鏁版潵瀹炵幇銆備互涓嬫槸鍏蜂綋鐨勬搷浣滄楠わ細棣栧厛锛屾渶甯歌鐨勬柟寮忔槸鍒╃敤`sysdate`鍑芥暟锛屾墽琛孲QL璇彞"SELECTsysdateFROMdual"锛岃繖灏嗚繑鍥炲綋鍓嶇殑鏃ユ湡鍜屾椂闂达紝濡傚浘鎵绀恒傚叾娆★紝瀵逛簬鏌愪簺鏁版嵁搴撳Gbase锛屽悓鏍峰彲浠ヤ娇鐢╜sysdate()`锛岀‘淇濆湪鍑芥暟鍚嶅悗鍔犳嫭鍙凤紝鎵ц绫讳技"SELECT...
绛旓細SELECT * FROM T WHERE DATE BETWEEN CURRENT DATE - (DAY(CURRENT DATE -1) AND CURRENT DATE;
绛旓細SELECT * FROM T WHERE DATE BETWEEN CURRENT DATE - (DAY(CURRENT DATE -1) AND CURRENT DATE;
绛旓細璇硶 DATEDIFF ( datepart , startdate , enddate )鍙傛暟 datepart 鏄瀹氫簡搴斿湪鏃ユ湡鐨勫摢涓閮ㄥ垎璁$畻宸鐨勫弬鏁銆備笅琛ㄥ垪鍑轰簡 Microsoft® SQL Server™ 璇嗗埆鐨勬棩鏈熼儴鍒嗗拰缂╁啓銆傛棩鏈熼儴鍒 缂╁啓 year yy, yyyy quarter qq, q Month mm, ...
绛旓細UNION ALL SELECT COL1 + 1 MONTHS, COL2 + 1 MONTHS FROM TEMP1 WHERE COL1 + 1 MONTHS <= DATE('2006-02-01'))SELECT YEAR(COL1) * 100 + MONTH(COL1) AS MONBEGIN,YEAR(COL2) * 100 + MONTH(COL2) AS MONEND FROM TEMP1 浣犲彧瑕佸皢璇彞閲岄潰鐨勬棩鏈鏇挎崲涓涓嬪氨鑳藉疄鐜颁綘鐨勮姹傘
绛旓細select days(TO_DATE('20130313','yyyy-mm-dd'))-days(TO_DATE('20130301','yyyy-mm-dd')) from sysibm.dual;涓涓SQL宸查獙璇侊紝鍙互鐭ヨ冻妤间富闇瑕
绛旓細寤轰竴涓瓨鍌ㄨ繃绋 鍦ㄩ噷闈㈠啓涓惊鐜氨琛屼簡 datetime := 20120407--璧风偣鍊 while datetime<20150428 loop --鎴鏃堕棿 sqlstr='insert into calendar(c_date) values('||datetime||')';execute immediate sqlstr;datetime=to_char(to_date(datetime,'yyyymmdd')+1,'yyyymmdd');--鍙栦笅涓澶 end loop;...
绛旓細1.鎷疯礉鍒DB2瀹㈡埛绔伐鍏蜂腑鐩存帴鎵ц 2.灏嗕笂闈㈢殑璇彞淇濆瓨涓簍est.db2鏂囦欢鏀惧埌浠绘剰鐩綍涓嬶紙姣斿D鐩樻牴鐩綍锛夛紝鐒跺悗鍦╟md杈撳叆db2cmd 鐒跺悗杈撳叆db2 -td@ -vf D锛歕test.db2鍗冲彲 3.鐩存帴鍐sql锛歝all PLName(瀛樺偍杩囩▼鍚嶅瓧) 锛圛N_ID,IN_ENTNAME,IN_REGNO,IN_PASSWORD,IN_LEREP,IN_CERTYPE,IN_CERNO,IN_...
绛旓細4銆佸浜巗ysbase鏁版嵁搴擄紝鍏惰幏鍙栧綋鍓嶆椂闂寸殑鍑芥暟鏄細select getdate()銆4銆DB2鏁版嵁搴鐣ユ湁涓嶅悓锛屽湪鑾峰彇褰撳墠鏃堕棿涓婏紝浣跨敤濡備笅鐨勬煡璇sql锛歋ELECT current timestamp FROM sysibm.sysdummy1銆5銆侀氬父锛屾垜浠湪鑾峰彇褰撳墠鏃堕棿鍚庯紝杩樻兂鑾峰緱褰撳墠鏃堕棿寰鍓嶆垨鑰呭線鍚庢帹涓娈垫椂闂鐨勬棩鏈锛屽浜嶥B2鏁版嵁搴擄紝褰撳墠鏃堕棿寰鍓嶆帹涓澶:...