Oracle: How can I select records ONLY from yesterd

Question

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.

Answer 1

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

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

Yesterday

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

Answer 2

trunc(tran_date) = trunc(sysdate -1)

Answer 3

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

Answer 4

Use:

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

Reference: 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.

Answer 5

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

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

Answer 6

If you want the timestamp for yesterday try something like:

(CURRENT_TIMESTAMP - INTERVAL '1' DAY)