select count(*) from test s where to_char(s.times,'YYYYMMDD') >= '2011-03-04' and to_char(s.times,'YYYYMMDD') <= '2011-03-14'; (注:s.times='2011-03-07 14:32:09')
结果为 0select count(*) from test s where to_char(s.times,'YYYYMMDD') >= '2011-03-04'
结果为 1select count(*) from test s where to_char(s.times,'YYYYMMDD') <= '2011-03-14';
结果为 0很奇怪,大概是这样的一个意思:
2011-03-07 >= 2011-03-04 true;
2011-03-07 >= 2011-03-14 true;2011-03-07 >= 2011-03-04 true;
2011-03-07 <= 2011-03-14 false;why ?
结果为 0select count(*) from test s where to_char(s.times,'YYYYMMDD') >= '2011-03-04'
结果为 1select count(*) from test s where to_char(s.times,'YYYYMMDD') <= '2011-03-14';
结果为 0很奇怪,大概是这样的一个意思:
2011-03-07 >= 2011-03-04 true;
2011-03-07 >= 2011-03-14 true;2011-03-07 >= 2011-03-04 true;
2011-03-07 <= 2011-03-14 false;why ?
结果为 0select count(*) from test s where s.times >= to_date('2011-03-14','yyyy-mm-dd hh24:mi:ss');
结果为 0select count(*) from test s where s.times <= to_date('2011-03-14','yyyy-mm-dd hh24:mi:ss');
结果为 1这是个什么原因 ?
to_char(s.times,'YYYYMMDD') 就把时间转换成'20110307'了
and s.times <= to_date('2011-03-14','yyyy-mm-dd hh24:mi:ss');
你这样写不就是s.times=to_date('2011-03-14','yyyy-mm-dd hh24:mi:ss')嘛而你的s.times是2011-03-07 14:32:09 肯定为0咯
后面的两个就不用解释了吧
select count(*) from test s where to_char(s.times,'YYYYMMDD') >= '2011-03-04'
结果为 1
select count(*) from test s where s.times <= to_date('2011-03-14','yyyy-mm-dd hh24:mi:ss');
结果为 1
呢?
都说了用to_char()之后就是字符串进行比较了
'20110307' 与'2011-03-14'进行比较
前面4位都是相同的2010 第五位一个是'0' 一个是'-'
select ascii('0'),ascii('-') from dual;ASCII('0') ASCII('-')
---------- ----------
48 45
从这个语句可以看出来'0'是大于'-'的 所以'20110307'大于'2011-03-14'
而s.times <= to_date('2011-03-14','yyyy-mm-dd hh24:mi:ss');
是时间进行比较 20110307当然要小于2011-03-14