How can I select records ONLY from yesterday?

sql database oracle plsql
Keith Myers picture Keith Myers · Oct 15, 2009 · Viewed 175.9k times · Source

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

Answer

OMG Ponies picture OMG Ponies · Oct 15, 2009

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.

Related questions

create table with sequence.nextval in oracle

i created a sequence using the following query, create sequence qname_id_seq start with 1 increment by 1 nocache; Now when i try to create a table which uses the above sequence, it is throwing the following error, Error report: SQL …

Read More
SQL recursive query on self referencing table (Oracle)

Lets assume I have this sample data: | Name | ID | PARENT_ID | ----------------------------- | a1 | 1 | null | | b2 | 2 | null | | c3 | 3 | null | | a1.d4 | 4 | 1 | | a1.e5 | 5 | 1 | | a1.d4.f6 | 6 | 4 | | a1.d4.g7 | 7 | 4 | | a1.e5.h8 | 8 | 5 | | a2.i9 | 9 | 2 | | a2.i9.j10| 10 | 9 | I would like to …

Read More
How to solve error: ORA-01406 fetched column value was truncated?

I am trying to execute this query: select * from gi_todo_isim WHERE d_insercao >= '04-JUL-13' AND d_insercao <= '25-JUL-13' However this I am getting this error back: ORA-01406 fetched column value was truncated …

Read More