Oracle日期時間范圍查詢 Sql代碼 /* 日期時間范圍查詢 */ ---------- 創建日期時間測試表----------------------------------------------------------- www.2cto.com create table testdatetime( ID integer not null PRimary KEY, createdate CHAR(10) NULL, startdate DATE NULL, enddate DATE NULL, buydate CHAR(10) NULL, usedate varchar2(20) NULL ) ; ------------------------ 日期時間范圍查詢 ------------------------------------ SELECT t.*,t.ROWID FROM testdatetime t ; --字段為：字符串類型（char），長度為:10 SELECT * FROM testdatetime t WHERE 1=1 AND t.createdate = '2011-07-01'; SELECT * FROM testdatetime t WHERE 1=1 AND t.createdate > '2011-07-01'; SELECT * FROM testdatetime t WHERE 1=1 AND t.createdate < '2011-07-01'; SELECT * FROM testdatetime t WHERE 1=1 AND t.createdate >= '2011-06-01' ANDt.createdate <= '2011-07-05'; www.2cto.com SELECT * FROM testdatetime t WHERE 1=1 AND to_date(t.createdate,'yyyy-MM-dd') between to_date('2011-06-01','yyyy-MM-dd') and to_date('20110705','yyyymmdd') ; ------------------------------------------------------------------- SELECT t.*,t.ROWID FROM testdatetime t ; --字段為：字符串類型（char），長度為:20 SELECT * FROM testdatetime t WHERE 1=1 AND t.usedate = '2011-07-01 10:00:00'; SELECT * FROM testdatetime t WHERE 1=1 AND t.usedate > '2011-07-01'; SELECT * FROM testdatetime t WHERE 1=1 AND t.usedate < '2011-07-01'; SELECT * FROM testdatetime t WHERE 1=1 AND t.usedate >= '2011-06-01' AND t.usedate <= '2011-07-05'; SELECT * FROM testdatetime t WHERE 1=1 AND to_date(t.usedate,'yyyy-MM-dd HH24:MI:SS')between to_date('2011-07-01 12:00:00','yyyy-MM-dd HH24:MI:SS') and to_date('20110702 12:00:00','yyyymmdd HH24:MI:SS') ; ------------------------------------------------------------------------- SELECT t.*,t.ROWID FROM testdatetime t ; --字段為：日期時間類型（date） www.2cto.com SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate = to_date('2011-07-01','yyyy-MM-dd'); SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate > to_date('2011-07-01','yyyy-MM-dd'); SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate < to_date('2011-07-01','yyyy-MM-dd'); SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate >= to_date('2011-07-01','yyyy-MM-dd')AND t.startdate <= to_date('2011-07-02','yyyy-MM-dd'); SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate between to_date('2011-07-01','yyyy-MM-dd') AND to_date('2011-07-02','yyyy-MM-dd'); ---------------------- SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate = to_date('2011-07-01 12:00:00','yyyy-MM-dd HH24:MI:SS'); SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate > to_date('2011-07-01 12:00:00','yyyy-MM-dd HH24:MI:SS'); SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate < to_date('2011-07-01 12:00:00','yyyy-MM-dd HH24:MI:SS'); SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate >= to_date('2011-07-01 01:00:00','yyyy-MM-dd HH24:MI:SS') AND t.startdate <= to_date('2011-07-01 23:00:00','yyyy-MM-dd HH24:MI:SS'); www.2cto.com SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate between to_date('2011-07-01 01:00:00','yyyy-MM-dd HH24:MI:SS') AND to_date('2011-07-01 23:00:00','yyyy-MM-dd HH24:MI:SS'); ----------------------- SELECT * FROM testdatetime t WHERE 1=1 AND to_char(t.startdate,'yyyy-MM-dd') = '2011-07-01'; SELECT * FROM testdatetime t WHERE 1=1 AND to_char(t.startdate,'yyyy-MM-dd') > '2011-07-01'; SELECT * FROM testdatetime t WHERE 1=1 AND to_char(t.startdate,'yyyy-MM-dd') < '2011-07-01'; www.2cto.com SELECT * FROM testdatetime t WHERE 1=1 AND to_char(t.startdate,'yyyy-MM-dd') >= '2011-07-01' AND to_char(t.startdate,'yyyy-MM-dd') <= '2011-07-02'; SELECT * FROM testdatetime t WHERE 1=1 AND to_char(t.startdate,'yyyy-MM-dd') between '2011-07-01' AND '2011-07-02';
