I have spent hrs searching the net to have an response to this...

This is what I presently have:

select  *
from    order_header oh
where   tran_date = sysdate-1

Thanks ahead of time.

Use:

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

Reference: TRUNC

Calling a function around the tran_date means the optimizer will not have the ability to make use of an index (presuming one is available) connected by using it. Some databases, for example Oracle, support function based indexes which permit carrying out functions around the data to reduce impact such situations, but IME DBAs will not allow these. And To be sure - they are not really necessary in cases like this.

trunc(tran_date) = trunc(sysdate -1)

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

If you do not support future dated transactions then something similar to this may work:

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