/* 日期时间范围查询 */---------- 创建日期时间测试表-----------------------------------------------------------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），长度为:10SELECT * FROM testdatetime t WHERE 1=1 AND t.createdate = '-07-01';SELECT * FROM testdatetime t WHERE 1=1 AND t.createdate > '-07-01';SELECT * FROM testdatetime t WHERE 1=1 AND t.createdate < '-07-01';SELECT * FROM testdatetime t WHERE 1=1 AND t.createdate >= '-06-01' AND t.createdate <= '-07-05';SELECT * FROM testdatetime t WHERE 1=1 AND to_date(t.createdate,'yyyy-MM-dd') between to_date('-06-01','yyyy-MM-dd') and to_date('0705','yyyymmdd') ;-------------------------------------------------------------------SELECT t.*,t.ROWID FROM testdatetime t ;--字段为：字符串类型（char），长度为:20SELECT * FROM testdatetime t WHERE 1=1 AND t.usedate = '-07-01 10:00:00';SELECT * FROM testdatetime t WHERE 1=1 AND t.usedate > '-07-01';SELECT * FROM testdatetime t WHERE 1=1 AND t.usedate < '-07-01';SELECT * FROM testdatetime t WHERE 1=1 AND t.usedate >= '-06-01' AND t.usedate <= '-07-05';SELECT * FROM testdatetime t WHERE 1=1 AND to_date(t.usedate,'yyyy-MM-dd HH24:MI:SS') between to_date('-07-01 12:00:00','yyyy-MM-dd HH24:MI:SS') and to_date('0702 12:00:00','yyyymmdd HH24:MI:SS') ;-------------------------------------------------------------------------SELECT t.*,t.ROWID FROM testdatetime t ;--字段为：日期时间类型（date）SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate = to_date('-07-01','yyyy-MM-dd');SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate > to_date('-07-01','yyyy-MM-dd');SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate < to_date('-07-01','yyyy-MM-dd');SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate >= to_date('-07-01','yyyy-MM-dd') AND t.startdate <= to_date('-07-02','yyyy-MM-dd');SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate between to_date('-07-01','yyyy-MM-dd') AND to_date('-07-02','yyyy-MM-dd');----------------------SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate = to_date('-07-01 12:00:00','yyyy-MM-dd HH24:MI:SS');SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate > to_date('-07-01 12:00:00','yyyy-MM-dd HH24:MI:SS');SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate < to_date('-07-01 12:00:00','yyyy-MM-dd HH24:MI:SS');SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate >= to_date('-07-01 01:00:00','yyyy-MM-dd HH24:MI:SS') AND t.startdate <= to_date('-07-01 23:00:00','yyyy-MM-dd HH24:MI:SS');SELECT * FROM testdatetime t WHERE 1=1 AND t.startdate between to_date('-07-01 01:00:00','yyyy-MM-dd HH24:MI:SS') AND to_date('-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') = '-07-01';SELECT * FROM testdatetime t WHERE 1=1 AND to_char(t.startdate,'yyyy-MM-dd') > '-07-01';SELECT * FROM testdatetime t WHERE 1=1 AND to_char(t.startdate,'yyyy-MM-dd') < '-07-01';SELECT * FROM testdatetime t WHERE 1=1 AND to_char(t.startdate,'yyyy-MM-dd') >= '-07-01' AND to_char(t.startdate,'yyyy-MM-dd') <= '-07-02';SELECT * FROM testdatetime t WHERE 1=1 AND to_char(t.startdate,'yyyy-MM-dd') between '-07-01' AND '-07-02';
