oracle日期时间范围查询,Oracle的日期时间范围查询-Oracle

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

作者 weishaoxiang

  • 0
    点赞
  • 2
    收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:游动-白 设计师:我叫白小胖 返回首页
评论
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值