Oracle 中关于 Between and 日期边界问题

之所以会写日期边界范围的问题,是因为我在查询日期的时候,刚开始数据都能正常获取,但是突然有些数据获取不到。刚开始以为是 Between and 的边界问题。但是经过一番折腾发现,发现是 Oracle 在对日期格式的字段进行查询时,会对字符串进行了隐式转换。

以下就是错误的查询语句

-- create_time 的时间为 2019/3/26 23:00:00
SELECT *
  FROM T_CLASS T
 WHERE T.CREATE_TIME BETWEEN '2019-03-01' AND '2019-03-26';

一、结论

  1. Oracle 对字符串进行日期转换时,如果是年月日的形式,即“2019-03-26”,将会被转为“2019-03-26 00:00:00”
  2. Oracle 在进行日期比较时,最好直接指定日期比较的格式,不要进行隐式转换

二、初始化数据

  1. 初始化表结构
create table T_CLASS
(
  class_id    NUMBER not null,
  class_name  VARCHAR2(100),
  create_time DATE
)
  1. 初始化表数据
insert into T_CLASS (CLASS_ID, CLASS_NAME, CREATE_TIME)
values (1, '一班', to_date('26-03-2019 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_CLASS (CLASS_ID, CLASS_NAME, CREATE_TIME)
values (2, '二班', to_date('26-03-2019 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_CLASS (CLASS_ID, CLASS_NAME, CREATE_TIME)
values (3, '三班', to_date('26-03-2019 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_CLASS (CLASS_ID, CLASS_NAME, CREATE_TIME)
values (4, '四班', to_date('26-03-2019 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));

insert into T_CLASS (CLASS_ID, CLASS_NAME, CREATE_TIME)
values (5, '五班', to_date('26-03-2019 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
  1. 查询结果
CLASS_IDCLASS_NAMECREATE_TIME
1一班2019/3/26 23:00:00
2二班2019/3/26 23:00:00
3三班2019/3/26 23:00:00
4四班2019/3/26 23:00:00
5五班2019/3/26 23:00:00

三、日期查询的几种形式

1.日期直接比较

查询语句

--隐式转换
SELECT * FROM T_CLASS T WHERE T.CREATE_TIME = '2019-03-26';

查询结果

CLASS_IDCLASS_NAMECREATE_TIME

2.日期转换为字符串后比较

查询语句

--显示转换
SELECT *
  FROM T_CLASS T
 WHERE TO_CHAR(T.CREATE_TIME, 'YYYY-MM-DD') = '2019-03-26';

查询结果

CLASS_IDCLASS_NAMECREATE_TIME
1一班2019/3/26 23:00:00
2二班2019/3/26 23:00:00
3三班2019/3/26 23:00:00
4四班2019/3/26 23:00:00
5五班2019/3/26 23:00:00

3.日期范围比较(边界值)

查询语句

--隐式转换
SELECT *
  FROM T_CLASS T
 WHERE T.CREATE_TIME BETWEEN '2019-03-01' AND '2019-03-26';

查询结果

CLASS_IDCLASS_NAMECREATE_TIME

4.日期范围比较(超过边界值)

--隐式转换
SELECT *
  FROM T_CLASS T
 WHERE T.CREATE_TIME BETWEEN '2019-03-01' AND '2019-03-27';

查询结果

CLASS_IDCLASS_NAMECREATE_TIME
1一班2019/3/26 23:00:00
2二班2019/3/26 23:00:00
3三班2019/3/26 23:00:00
4四班2019/3/26 23:00:00
5五班2019/3/26 23:00:00

5.日期范围比较(格式化)

查询语句

--显示转换
SELECT *
  FROM T_CLASS T
 WHERE TO_CHAR(T.CREATE_TIME, 'YYYY-MM-DD') 
    BETWEEN '2019-03-01' AND '2019-03-26';

查询结果

CLASS_IDCLASS_NAMECREATE_TIME
1一班2019/3/26 23:00:00
2二班2019/3/26 23:00:00
3三班2019/3/26 23:00:00
4四班2019/3/26 23:00:00
5五班2019/3/26 23:00:00

四、分析

对 Oracle 日期字段的查询,建议通过 to_char 或者 to_date 这两个方法将相应的数据进行格式化。
对数据进行格式化的好处:

  1. 可以使查询语句一目了然。
  2. 避免因为隐式转换带来的业务逻辑上的风险。
  3. 避免了因为调试问题而导致的时间浪费(如果不是熟悉Oracle,边界问题也是比较难发现的)

如果文章有帮助到了你,欢迎点赞、转发。

如果文章有错误的地方,欢迎留言交流。

兔子兔兔兔 2121 21 nux kdj
1111
已标记关键词 清除标记
表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 护眼 设计师:闪电赇 返回首页
实付 89.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值