CODEWITHSUNDEEP
sqloracle-database
Keith Myers
Keith Myers

Reputation: 1369

How can I select records ONLY from yesterday?

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

Upvotes: 42

Views: 211905

Answers (6)

Aliuk
Aliuk

Reputation: 1359

If you want the timestamp for yesterday try something like:

(CURRENT_TIMESTAMP - INTERVAL '1' DAY)

Upvotes: 0

netsuvi
netsuvi

Reputation: 11

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)

Upvotes: 1

Henry Gao
Henry Gao

Reputation: 4936

trunc(tran_date) = trunc(sysdate -1)

Upvotes: 21

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

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

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332541

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.

Upvotes: 77

ninesided
ninesided

Reputation: 23263

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

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

Upvotes: 2

Related Questions