How can I select records ONLY from yesterday?

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

Solution 1

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.

Solution 2

trunc(tran_date) = trunc(sysdate -1)

Solution 3

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

Solution 4

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

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

Solution 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

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

Yesterday

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

View more solutions

Comments

[ninesided]

If, as I presume, there's an index on tran_date on which this query relies, calling a funtion on the indexed column like this will decimate the performance of the query.

[Henry Gao]

then rexem's method will be helpful.

[Rob van Laarhoven]

In that case you can add a function based index: create index index_name on table_name(trunc(tran_date));

[OMG Ponies]

@Robert: IME, most DBAs won't let you. And I have to agree, because it's not really necessary.

[Rob van Laarhoven]

Henry is right : rexem's solution is probably better (less impact) than adding a functio based index...

[Justin Cave]

One small caveat-- you may need to subtract 1 second from the upper bound. Otherwise, if you have a TRAN_DATE which is today at midnight, it will be returned in the query for rows inserted yesterday. I wouldn't expect that to be the desired behavior.

[Jeffrey Kemp]

Oracle supports a wide range of operators directly on dates, without having to resort to doing string comparisons.

[sbrbot]

Take into account that this solution does not take daylight saving into account. This solution assumes that every day lasts 24h, on daylight saving 'day' lasts 23h or 25h.

[NealWalters]

I get "ORA-00936 missing expression" on the date. See stackoverflow.com/questions/19143376/…