Oracle日期时间范围查询
Sql代码
/* 日期时间范围查询 */
———- 创建日期时间测试表———————————————————–
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 = ‘-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)，长度为:20
SELECT * 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’;
作者 weishaoxiang
