主页 / dba / 问题 / 27449
Accepted
fa1c0n3r
Asked: 2012-10-24 02:35:22 +0800 CST

日期类型的简单查询需要显式转换为日期的 to_date

  • 772

我的问题是，我有一个表，其中包含一个名为 myDateColumn 的列（例如），它的类型是日期：

我可以确认desc myTable包含这一行：

myDateColumn  NOT NULL     DATE

但是，当我尝试选择特定日期之间的所有数据时，例如：

select * from myTable 
where myDateColumn 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

无论如何，我都会得到空结果，即使我确信某些数据存在于这个特定的日期范围内。

我发现的解决方法是将 myDateColumn 显式转换为日期：

select myDateColumn from myTable 
where to_date(myDateColumn) 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

它返回正确的结果，但它已经是一个日期类型！为什么在进行between逻辑比较之前需要将日期列显式转换为日期？

我问的原因是因为这个 select/where 将成为存储过程的一部分，选择特定日期范围内的数据在按日期分区的大表上运行，超过一千万行，如果我可以避免显式转换每行的每个 myDateColumn，那么理论上我也许可以节省一些查询时间。

或者是否有更正确的方法来运行此比较以选择日期范围内的数据？

谢谢你。

更新：Leigh 在答案中建议诊断表是否与他的查询不一致。他的查询结果是：

myDateColumn    TO_DATE(myDateColumn)   TO_CHAR(myDateColumn,'DD-MON-YYHH.MI.SSPM') TO_CHAR(myDateColumn,'YYYY')  
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011
14-Feb-11   14-Feb-11   14-FEB-11 12.00.00 AM   0011

表明该between子句不起作用的原因是因为没有数据会落在查询间隔内，因为年份是 0011，在罗马帝国时期 :)

多谢你们。

oracle oracle-11g-r2

2 个回答

  1. Oracle 中的所有日期都有一个时间部分。

    我很确定您希望包含第 13 和第 15 之间的所有行。但是，当您编写时：

    BETWEEN to_date('13-02-2011', 'DD-MM-YYYY') AND TO_DATE('15-02-2011', 'DD-MM-YYYY')

    您只选择15-02-2011午夜之前的所有日期。当天晚些时候的所有行都不会被选中。

    发生的情况是，当您TO_DATE在日期上使用时，它将转换为 VARCHAR2，然后再转换回日期。最终效果将是截断您的日期格式，如下所示：

    SQL> alter session set nls_date_format='dd/mm/yyyy';

Session altered.

SQL> select to_char(to_date(sysdate), 'dd/mm/yyyy hh24:mi:ss') trunc,
  2         to_char(sysdate, 'dd/mm/yyyy hh24:mi:ss') not_trunc
  3    from dual;

TRUNC               NOT_TRUNC
------------------- -------------------
23/10/2012 00:00:00 23/10/2012 15:15:32

    所以你的两个子句根本不等价。第二个选择 2 月 15 日的全天，而第一个只选择当天的第一秒。

    我建议您在编写日期范围时使用以下结构：

    WHERE dat >= to_date('13-02-2011', 'DD-MM-YYYY')
  AND dat < to_date('16-02-2011', 'DD-MM-YYYY')

    也不要在代码中使用MON或DAY格式化，因为它们取决于语言。不要使用YY，因为这可能会令人困惑（听说过 Y2k 错误吗？）。当然，这两种格式都可以向您的用户显示信息。

    • 3
  2. Best Answer

    根据您的假设，您认为没有必要这样做是正确的。通常这意味着应该重新检查假设（除非存在数据损坏或错误）。如果您运行以下命令，您应该从第一个查询中获得与第二个相同的结果：

    DROP TABLE t1;
CREATE TABLE t1 AS (
   SELECT to_date('01-FEB-2011','DD-MON-YYYY')+level myDateColumn 
   FROM dual CONNECT BY level <=120);

select * from t1 
where myDateColumn 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

select myDateColumn from t1 
where to_date(myDateColumn) 
between to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

    要检查一些假设，您能显示以下查询的结果吗？

    SELECT myDateColumn, to_date(myDateColumn)
   , to_char(myDateColumn,'DD-MON-YY HH.MI.SS PM'), to_char(myDateColumn,'YYYY') 
FROM myTable
WHERE to_date(myDateColumn)
BETWEEN to_date('13-FEB-11', 'DD-MON-YY') AND TO_DATE('15-FEB-11', 'DD-MON-YY');

    前三列都应显示相同的信息，最后一列应验证年份是否正确。

    • 1

相关问题