oracle时间查询的

Oracle日期时间范围查询

 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';  

 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）  

   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');    

   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';    

   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';