DB2怎么查询在两个日期之间数据 SQL(DB2)查询时,怎么限定日期格式?月份和日显示两位数...
DB2\u4e2d\uff0c\u5982\u4f55\u67e5\u8be2\u51fa\u4e0e\u67d0\u4e2a\u65f6\u95f4\u6700\u63a5\u8fd1\u7684\u8bb0\u5f55\u8bf7\u53c2\u8003\u5982\u4e0bSQL\uff0c\u5177\u4f53\u903b\u8f91\u662f\uff1a\u5dee\u503c\u7684\u7edd\u5bf9\u503c\u5c0f\u5230\u5927\u6392\u5e8f\uff0c\u53d6\u7b2c\u4e00\u884c\u5373\u53ef\u3002
\u5982\u679c\u9700\u8981\u5176\u4ed6\u5217\u7684\u503c\uff0c\u628a\u53d6\u503c\u653ewhere\u4e2d\u2026\u2026 \u8fd9\u6837\u5c31\u7b97\u6709\u91cd\u590d\u503c\uff08\u5dee\u503c\u7684\u7edd\u5bf9\u503c\u76f8\u540c\uff09\u4e5f\u80fd\u63ea\u51fa\u6765~
select T from table(select T, abs($T_STR-T) as diff from A) order by diff fetch first 1 rows only
\u4f8b\u5b50\uff1a
db2 => select * from a
T
--------------------------
2014-04-02-01.00.00.000000
2014-05-02-01.00.00.000000
2014-06-02-01.00.00.000000
3 record(s) selected.
db2 => values timestamp('2014-05-03-01.00.00.00000')
1
--------------------------
2014-05-03-01.00.00.000000
1 record(s) selected.
db2 => select T from table(select T, abs('2014-05-03-01.00.00.00000'-T) as diff from A) order by diff fetch first 1 rows only
T
--------------------------
2014-05-02-01.00.00.000000
1 record(s) selected.
db2 =>
to_date(ORDER_DATE,'YYYY-MM')
select * from userid.temptzxx where scrq between '2003-01-01 ' and '2003-09-01 'select * from userid.temptzxx where scrq > = '2003-01-01 ' and scrq <= '2003-09-01 '
绛旓細涓嶇煡閬撲綘閭d釜鏌ヨ锛 鑳戒笉鑳藉姞涓涓 Row_Number 鐨勫鐞嗐傛垨鑰呮湁浠涔 閫掑鐨勬暟鍊煎湪閲岄潰鐨勩傚鏋滄湁鐨勮瘽锛 鍙互鍙傝冧笅闈㈣繖绉嶅鐞嗘柟寮忥細娴嬭瘯鏁版嵁.CREATE TABLE temp ( year INT, salary INT);INSERT INTO temp VALUES(2000, 1000);INSERT INTO temp VALUES(2001, 2000);INSERT INTO temp...
绛旓細select count(*) fromtable where req_time between current time - 1 hours and current time
绛旓細鑾峰彇浠婂ぉ鏄槦鏈熷嚑鐨勬柟娉曪細SELECT DAYNAME(CURRENT DATE) FROM SYSIBM.SYSDUMMY1 鑾峰彇涓嬩釜鏄熸湡涓鐨鏃ユ湡锛歋ELECT CURRENT DATE + (8-(SELECT DAYOFWEEK_ISO(CURRENT DATE) FROM SYSIBM.SYSDUMMY1)) DAYS FROM SYSIBM.SYSDUMMY1 甯屾湜鑳藉府鍒颁綘锛岀浣犲涔犳剦蹇紒
绛旓細db2涓殑鏃堕棿鍑芥暟鏈 timestamp, date,month绛夛紝鍙互婊¤冻浣犵殑瑕佹眰銆傚亣濡備綘鐨勮〃閲屾湁鏃堕棿鍒楋紝閭d箞鍙互浠巘imestamp閲屾彁鍙杫ear,month绛夈俿elect month(current timestamp) from sysibm.sysdummy1 杩欏氨琛ㄧず浠庡綋鍓嶇殑鏃堕棿閲屾彁鍙栧嚭鏈堜唤锛鏌ヨ缁撴灉涓7锛岃〃绀轰竷鏈堜唤銆倃here month(mydatecol)=month(current date)-1 ...
绛旓細寰堜箙娌℃湁鐢ㄨ繃db2浜嗭紝鏈夌偣蹇樹簡锛屽ソ鍍廳ays閲岄潰涓嶈兘鐢╯elect瀛愬彞鐨勫惂锛屼綘鎶婇噷闈elect瀛愬彞鏀规垚 to_date('20121118','yyyymmdd')璇曡瘯鐪
绛旓細select * from 琛ㄥ悕 where 瀛楁鍚 in ( 鎸囧畾鍊1锛屾寚瀹氬2锛屾寚瀹氬3锛...)
绛旓細SELECT * FROM TAB WHERE TAB.DTE BETWEEN CAST( CAST(DATE ('2010-4-1')+1 MONTHS AS CHAR(8))|| '01 ' AS DATE) - 1 DAY AND CAST( CAST(DATE ('2010-10-1')+1 MONTHS AS CHAR(8))|| '01 ' AS DATE) - 1 DAY;璇曡瘯杩欎釜锛屼篃璁告槸浣犻渶瑕佺殑 ...
绛旓細update t set f4=(select c3 from (select t1.f1 c1,t2.f1 c2, dense_rank() over(order by t2.f1) c3 from t t1 join t t2 on t1.f3=t2.f3 and t1.f1>t2.f1) s where t.f1 in (s.c1,s.c2))浠ヤ笂鍋囪琛ㄥ悕涓簍锛屽垪1-4鐨勫垪鍚嶅垎鍒负f1銆乫2銆乫3銆乫4 ...
绛旓細DB2涓父鐢ㄥ懡浠よ瑙o細鍏抽棴DB2: db2stop 鎴 db2stop force銆傚湪鑴氭湰涓紝鎺ㄨ崘鍙屽啓浠ラ槻璇搷浣滐紝濡傦細db2stopdb2stop force鍚姩DB2: db2start鍒涘缓鏁版嵁搴: db2 create db 鎴 db2 create db using codeset GBK territory CN鍒犻櫎鏁版嵁搴: db2 drop db 锛屾墽琛屽墠纭繚鏃犺繛鎺ワ紝蹇呰鏃堕噸鍚疍B2鏂紑鏁版嵁搴撹繛鎺: db2 ...
绛旓細4銆佺劧鍚庯紝瀵逛簬sysbase鏁版嵁搴擄紝鑾峰彇褰撳墠鏃堕棿鐨勫嚱鏁版槸锛氣渟elect getdate()鈥濓紝濡備笅鍥炬墍绀恒5銆 闅忓悗锛DB2鏁版嵁搴撶暐鏈変笉鍚屻 瑕佽幏鍙栧綋鍓嶆椂闂达紝璇蜂娇鐢ㄤ互涓鏌ヨsql锛氣淪ELECT current timestamp FROM sysibm.sysdummy1鈥濓紝濡備笅鍥炬墍绀恒6銆佹渶鍚庯紝鍦ㄨ幏鍙栧綋鍓嶆椂闂翠箣鍚庯紝杩樺笇鏈涘皢褰撳墠鏃堕棿鏃ユ湡鍚戝墠鎴栧悜鍚庝竴娈...