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

Question

-- 最近经常看到有人在某段时间区间上喜欢用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(*)
----------
         6eygle@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 这一时刻的记录也包括在内啦!

Answer 1

以前用的很多，直到前几天我始终发现多两条数据的时候，我也开始不使用 between and了。

Answer 2

呵呵，够细心，不过between and 本身没错，只不过用在这种地方不合适而已。

Answer 3

--其实并不是人家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，。--这里你提出来，应该是给大家一个警告，遇到一些问题的时候，需要仔细考虑，不要马虎。

Answer 4

--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说的一样。

Answer 5

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

Answer 6

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

Answer 7

楼主说的很对，但是好像最好写成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')

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

知道这个问题,
但因为公司的数据只会在白天产生,所以这个对我的影响不大但我通常会写成
发证日期 between to_date('2010-01-01','yyyy-mm-dd') and to_date('2010-12-31 23:59:59','yyyy-mm-dd hh24:mi:ss')

Answer 12

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