sql语句关于时间范围的筛选

1.字段为：字符串类型(char)长度为:10SELECT * 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 WHE

Somg_

10967人浏览 · 2021-10-21 13:36:14

Somg_ · 2021-10-21 13:36:14 发布

1.字段为：字符串类型(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’;

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

_____________________________________________________________________________

2字段为：字符串类型(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’) ;

____________________________________________________________________________

3字段为：日期时间类型(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’; 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’;