Oracle实现时间区间查询

冷不防 2022-05-27 02:20 528阅读 0赞

非常有用的总结,全部是干活,收藏![微笑][smile.gif]

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' AND  
t.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';

[smile.gif]: https://static-blog.csdn.net/xheditor/xheditor_emot/default/smile.gif