-- 查看某天(或某个时间区间)的数据，请别用between ... and ... --

luoyoumou 2011-03-24 09:47:20

-- 最近经常看到有人在某段时间区间上喜欢用between ... and ... ，其实，可以直接地说：这种用法是错误的!



-- 查看某一天的数据，或某一段时间内的数据，其实是一个左闭、右开的区间内的数据；



-- 例如：我要查一张表 2011年3月11日到2011年3月24日内所生成的数据，其区间应该为[2011-03-11 00:00:00, 2011-03-25 00:00:00)

-- 即：不包括右边2011-03-25 00:00:00时间点的值!



-- 所以，请看如下：



create table t(cdate date);

insert into t(cdate) values(to_date('2011-03-23 00:00:00','yyyy-mm-dd hh24:mi:ss'));

insert into t(cdate) values(to_date('2011-03-23 11:09:25','yyyy-mm-dd hh24:mi:ss'));

insert into t(cdate) values(to_date('2011-03-24 00:00:00','yyyy-mm-dd hh24:mi:ss'));

insert into t(cdate) values(to_date('2011-03-24 02:03:45','yyyy-mm-dd hh24:mi:ss'));

insert into t(cdate) values(to_date('2011-03-24 10:37:03','yyyy-mm-dd hh24:mi:ss'));

insert into t(cdate) values(to_date('2011-03-24 20:55:17','yyyy-mm-dd hh24:mi:ss'));

insert into t(cdate) values(to_date('2011-03-24 23:59:59','yyyy-mm-dd hh24:mi:ss'));

insert into t(cdate) values(to_date('2011-03-25 00:00:00','yyyy-mm-dd hh24:mi:ss'));

insert into t(cdate) values(to_date('2011-03-25 01:44:22','yyyy-mm-dd hh24:mi:ss'));



-- 查看2011年24日生成的数据



-- 方法一：用 ... and ...

eygle@SZTYORA> select count(*) from t

  2  where cdate>=to_date('2011-03-24','yyyy-mm-dd')

  3    and cdate<to_date('2011-03-25','yyyy-mm-dd');



  COUNT(*)

----------

         5



-- 方法二：用between ... and ...

eygle@SZTYORA> select count(*) from t

  2  where cdate between to_date('2011-03-24','yyyy-mm-dd')

  3    and to_date('2011-03-25','yyyy-mm-dd');



  COUNT(*)

----------

         6



eygle@SZTYORA> select * from t

  2  where cdate between to_date('2011-03-24','yyyy-mm-dd')

  3    and to_date('2011-03-25','yyyy-mm-dd')

  4  order by cdate;



CDATE

-------------------

2011-03-24 00:00:00

2011-03-24 02:03:45

2011-03-24 10:37:03

2011-03-24 20:55:17

2011-03-24 23:59:59

2011-03-25 00:00:00



已选择6行。



-- 可见方法二用between ... and ... 是错误的，它将2011-03-25 00:00:00 这一时刻的记录也包括在内啦!

...全文

913 30 打赏收藏转发到动态举报

写回复

用AI写文章

30 条回复

切换为时间正序

请发表友善的回复…

发表回复

bzr2004 2011-03-29

打赏
举报

在这种情况下，我一直用的to_date这种方法，虽然麻烦一点，但很有效，只是有些date类型数据需要用trunc转换一下

archwuke1 2011-03-29

打赏
举报

知道这个问题,
但因为公司的数据只会在白天产生,所以这个对我的影响不大

但我通常会写成
发证日期 between to_date('2010-01-01','yyyy-mm-dd') and to_date('2010-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')

t_bug 2011-03-29

打赏
举报

哎呀，看来以后要注意了，要多想一想再使用between ... and 了

horizonlyhw 2011-03-29

打赏
举报

学习~~

vber1010 2011-03-29

打赏
举报

我觉得这只是个需求问题。查看某一天的数据，或某一段时间内的数据，这个时间区间是如何开闭的还是要具体需求定论。

xiaofeixia09 2011-03-28

打赏
举报

学到新东西了，以前还真没注意

a735194357 2011-03-28

打赏
举报

区间取值between and 原来是闭区间以前没注意

doer_ljy 2011-03-28

打赏
举报

哈哈，对于'YYYY-MM-DD'格式的字符串形日期。
我一般就直接比较字符串了。
因为这个原因，不用between and ,惭愧惭愧!

feiyu107 2011-03-27

打赏
举报

楼主说的很对，但是好像最好写成where cdate>=to_date('2011-03-24 00:00:00','yyyy-mm-dd hh24:mi:ss') and cdate<=to_date('2011-03-24 23:59:59','hh24:mi:ss')

hyee 2011-03-27

打赏
举报

我觉得between..and比 x>=... and x<...要简洁得多。使用between <start> and <end>-1e-5不就得了。无所谓错误，只在于会不会用

zhengpeiyong 2011-03-25

打赏
举报

嗯，这个开闭区间还是知道的

碧水幽幽泉 2011-03-25

打赏
举报

罗哥oracle工作处事严谨，值得学习！

304的的哥 2011-03-25

打赏
举报



--between .. and ..:一直都是返回一个闭区间内的数据,下面是几个测试

with t as(

 select 10 num from dual union all

 select 45 from dual union all

 select 1 from dual union all

 select 8 from dual union all

 select 24 from dual)

select * from t

where num between 8 and 24;

--找出在区间[8,24]的数据

/*

       NUM

----------

        10

         8

        24

*/

with t as(

 select 'a' chr from dual union all

 select 'e' from dual union all

 select 'q' from dual union all

 select 'r' from dual union all

 select 'p' from dual union all

 select 'y' from dual)

select * from t

where chr between 'e' and 'r';

--找出在区间[e,r]的字母

/*

CHR

---

e

q

r

p

*/

create table date_test(

       date_1 date);

insert into date_test(date_1)

values(to_date('2011-01-02 00:01:55','yyyy-mm-dd hh24:mi:ss'));

insert into date_test(date_1)

values(to_date('2011-01-02 23:59:59','yyyy-mm-dd hh24:mi:ss'));

insert into date_test(date_1)

values(to_date('2011-02-01 12:56:59','yyyy-mm-dd hh24:mi:ss'));

insert into date_test(date_1)

values(to_date('2011-02-28 23:00:00','yyyy-mm-dd hh24:mi:ss'));

insert into date_test(date_1)

values(to_date('2011-03-25 07:25:42','yyyy-mm-dd hh24:mi:ss'));



--找出在区间[2011-01-02 23:59:59,2011-02-28 23:00:00]之间的数据



SQL> select to_char(date_1,'yyyy-mm-dd hh24:mi:ss') from date_test

  2  where date_1

  3        between to_date('2011-01-02 23:59:59') and

  4        to_date('2011-02-28 23:00:00');

--

TO_CHAR(DATE_1,'YYYY-MM-DDHH24'

-------------------------------

2011-01-02 23:59:59

2011-02-01 12:56:59

2011-02-28 23:00:00

--



SQL> select count(*) from date_test

  2  where date_1

  3        between to_date('2011-01-02 23:59:59') and

  4        to_date('2011-02-28 23:00:00');



  COUNT(*)

----------

         3



--关于date类型的比较,关键是格式转换的影响，就像paddy说的一样。

iihero_ 2011-03-25

打赏
举报

这个确实是容易犯错误的地方

gelyon 2011-03-24

打赏
举报



--其实并不是人家between ..and..是错的，

--1、对于时间的between..and..来说，真正意义上来讲是时间格式或者是to_date的用法问题，

--to_date(sysdate,'yyyy-mm-dd')这样转换后，时分秒变成默认00:00:00了，这样就可能用between..and..造成一些区间问题，比如少数据等。

--所以真正该考虑的是to_date格式转换的影响，是否适合用between..and..



--2、对于其它离散数据求区间来说，between..and..才发挥了它的作用，比如1，2，3，4，。。。。



--这里你提出来，应该是给大家一个警告，遇到一些问题的时候，需要仔细考虑，不要马虎。