Oracle日期“Between”查询

Question

我正在使用Oracle数据库，想要执行一个查询来检查两个日期之间的数据。

NAME               START_DATE    
-------------    ------------- 
Small Widget       15-JAN-10 04.25.32.000000 PM      
Product 1          17-JAN-10 04.31.32.000000 PM  



select * from <TABLENAME> where start_date  
BETWEEN '15-JAN-10' AND '17-JAN-10'

但是我从上面的查询中没有得到任何结果。我认为我必须使用"like"和"%"。但是我不知道在哪里使用它们。请给我一些提示。

- Gnaniyar Zubair

Answer 1

从你的输出来看，似乎你已将 START_DATE 定义为时间戳。如果它是普通日期，Oracle 就能够处理隐式转换。但由于它不是，你需要显式地将这些字符串强制转换为日期。

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'
  2  /

Session altered.

SQL>
SQL> select * from t23
  2  where start_date between '15-JAN-10' and '17-JAN-10'
  3  /

no rows selected

SQL> select * from t23
  2  where start_date between to_date('15-JAN-10') and to_date('17-JAN-10')
  3  /

WIDGET                          START_DATE
------------------------------  ----------------------
Small Widget                    15-JAN-10 04.25.32.000    

SQL> 

但我们仍然只得到一行结果。这是因为START_DATE具有时间元素。如果我们不指定时间组件，Oracle会将其默认为午夜。对于BETWEEN的from端来说这没问题，但对于until端来说不是：

SQL> select * from t23
  2  where start_date between to_date('15-JAN-10') 
  3                       and to_date('17-JAN-10 23:59:59')
  4  /

WIDGET                          START_DATE
------------------------------  ----------------------
Small Widget                    15-JAN-10 04.25.32.000
Product 1                       17-JAN-10 04.31.32.000

SQL>

编辑

如果您无法传递时间组件，则有几种选择。一种是更改WHERE子句以从条件中删除时间元素：

where trunc(start_date) between to_date('15-JAN-10') 
                            and to_date('17-JAN-10')

这可能会影响性能，因为它会使得基于 START_DATE 的 B-tree 索引失效。你需要构建一个函数基于的索引来代替它。

或者你可以在代码中将时间元素添加到日期中。

where start_date between to_date('15-JAN-10') 
                     and to_date('17-JAN-10') + (86399/86400) 

由于这些问题，许多人更喜欢避免使用 BETWEEN ，而是通过检查日期边界来实现：

where start_date >= to_date('15-JAN-10') 
and start_date < to_date('18-JAN-10')

- APC

---

我无法仅传递日期而不是时间。 - Gnaniyar Zubair

Answer 2

你需要将这些字符串转换为实际日期，尝试使用以下代码：

SELECT *
FROM <TABLENAME>
WHERE start_date BETWEEN TO_DATE('2010-01-15','YYYY-MM-DD') AND TO_DATE('2010-01-17', 'YYYY-MM-DD');

按照指定格式进行编辑的修改：

SELECT *
FROM <TABLENAME>
WHERE start_date BETWEEN TO_DATE('15-JAN-10','DD-MON-YY') AND TO_DATE('17-JAN-10','DD-MON-YY');

- Chad Birch

---

谢谢您的迅速回复。但我是从操作文件中以“17-JAN-10”这种格式获取From和To日期的。所以我试过像这样：SELECT * FROM uson_assetaccess WHERE accessdate BETWEEN TO_DATE('17-FEB-10', 'dd-MMM-yy') AND TO_DATE('17-FEB-10', 'dd-MMM-yy');但是我遇到了一个错误：ORA-01821：日期格式无法识别。 - Gnaniyar Zubair

你只需要使用不同的格式说明符，将“MMM”替换为“MON”，请参考我的编辑。 - Chad Birch

非常感谢您的快速响应。但是我已经使用了那个查询，但现在日期格式错误已经消失了。现在我得到了“没有发现数据”的错误，即使表中有数据。我想在表格中日期带有时间，但我们只发送日期。是因为这个原因吗？请澄清一下。 - Gnaniyar Zubair

这取决于你关注的时间段。我认为只指定日期会让它从/到那一天的午夜（开始）进行。如果您希望包括1月17日的任何内容，则需要添加时间，因此使查询的第二部分为：AND TO_DATE('17-JAN-10 23:59:59'，'DD-MON-YY HH: MI: SS')。 - Chad Birch

我无法在筛选日期时获取时间。 - Gnaniyar Zubair

我仍然遇到问题...我的查询是：SELECT * FROM uson_assetaccess WHERE accessDate BETWEEN TO_DATE('17-FEB-10','DD-MON-YY') AND TO_DATE('17-FEB-10','DD-MON-YY');在Oracle数据库中，字段为“17-FEB-10 04.25.32.000000 PM”作为时间戳。因此它与之不匹配。如何解决这个问题？ - Gnaniyar Zubair

Answer 3

正如APC所指出的，你的start_date列似乎是一个TIMESTAMP类型，但它也可能是一个带有本地时区或带有时区的TIMESTAMP数据类型。如果你的数据库服务器与你不在同一时区，这可能会影响到你对数据进行的任何查询。然而，让我们保持简单，假设你和你的服务器在同一个时区内。首先，为了让你更有信心，检查一下start_date是否是一个TIMESTAMP数据类型。

使用SQLPlus DESCRIBE命令（或你的IDE中的等效命令）来验证这个列是一个TIMESTAMP数据类型。

例如：

DESCRIBE mytable 应该报告：

Name        Null? Type
----------- ----- ------------
NAME              VARHAR2(20) 
START_DATE        TIMESTAMP

如果报告为Type = TIMESTAMP，则可以使用最简单的TO_TIMESTAMP日期转换查询您的日期范围，无需任何参数（或图片）。

我们使用TO_TIMESTAMP确保优化器考虑START_DATE列上的任何索引。APC的答案还指出可以在此列上创建基于函数的索引，并且这将影响SQL谓词，但我们无法在此查询中发表评论。如果您想知道如何查找已应用于表的索引，请发布另一个问题，我们可以单独回答。

因此，假设有一个在start_date上的索引，它是TIMESTAMP数据类型，您希望优化器考虑它，那么您的SQL将是：

select * from mytable where start_date between to_timestamp('15-JAN-10') AND to_timestamp('17-JAN-10')+.9999999

+.999999999非常接近但并不完全等于1，因此对于17-JAN-10的转换将尽可能接近那一天的午夜，因此您的查询返回了两行。

数据库会将BETWEEN看作从15-JAN-10 00:00:00.0000000到17-JAN-10 23:59:59.99999，因此无论时间戳的时间组件如何，它都将包括2010年1月15日、16日和17日的所有日期。

- Darren Atkinson

Answer 4

以下查询也可以使用：

select * 
  from t23
 where trunc(start_date) between trunc(to_date('01/15/2010','mm/dd/yyyy')) and trunc(to_date('01/17/2010','mm/dd/yyyy'))

- Ajay

---

如果表格很大且日期列已经建立索引，应该避免使用表达式，而是使用 start_date between to_date('01/15/2010','mm/dd/yyyy') and to_date('01/17/2010','mm/dd/yyyy') + 1。 - Dmitriy

Answer 5

日期范围查询

SELECT *
    FROM emp
    WHERE HIREDATE between to_date (to_char(sysdate, 'yyyy') ||'/09/01', 'yyyy/mm/dd')
    AND to_date (to_char(sysdate, 'yyyy') + 1|| '/08/31', 'yyyy/mm/dd');

- user3496353