甲骨文:我如何只从昨天开始选择记录?

I've spent hours searching the web for an answer to this question...

我花了好几个小时在网上搜索这个问题的答案……

Here's what I currently have:

以下是我目前所拥有的:

select  *
from    order_header oh
where   tran_date = sysdate-1

Thanks in advance.

提前谢谢。

5 个解决方案

#1

Use:

使用:

AND oh.tran_date BETWEEN TRUNC(SYSDATE - 1) AND TRUNC(SYSDATE) - 1/86400

Reference: TRUNC

参考:TRUNC

Calling a function on the tran_date means the optimizer won't be able to use an index (assuming one exists) associated with it. Some databases, such as Oracle, support function based indexes which allow for performing functions on the data to minimize impact in such situations, but IME DBAs won't allow these. And I agree - they aren't really necessary in this instance.

在tran_date上调用函数意味着优化器无法使用与其关联的索引(假设存在索引)。一些数据库(如Oracle)支持基于函数的索引，这些索引允许在数据上执行功能，以尽量减少这种情况下的影响，但是IME dba不允许这样做。我同意，在这种情况下，它们并不是必须的。

#2

trunc(tran_date) = trunc(sysdate -1)

#3

If you don't support future dated transactions then something like this might work:

如果你不支持未来的交易，那么类似的事情可能会发生:

AND oh.tran_date >= trunc(sysdate-1)

#4

to_char(tran_date, 'yyyy-mm-dd') = to_char(sysdate-1, 'yyyy-mm-dd')

#5

This comment is for readers who have found this entry but are using mysql instead of oracle! on mysql you can do the following: Today

这条评论是写给那些已经找到这个条目但正在使用mysql而不是oracle的读者的!在mysql上，您可以做以下事情:今天

SELECT  * 
FROM 
WHERE date(tran_date) = CURRENT_DATE()

Yesterday

昨天

SELECT  * 
FROM yourtable 
WHERE date(tran_date) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)

#1