tdate issue I'm facing in SQL query

Question

While fetching count from table by using following query

Select count(*)  
from tab 
where tdate = '17-05-19' ---> output 0

or

Select count(*)  
from tab 
where trunc(tdate) = '17-05-19'  ---->output 0

If I use:

Select count(*)  
from tab 
where tdate  >sysdate - 1   ---> it returns some count(yesterday+some of the today txn)

But here I want only yesterday txn whenever I fire this query.

Answer 1

But here I want only yesterday txn whenever I fire this query.

You may use this.

Select count (*) from tab where
         tdate >= TRUNC(SYSDATE) - 1 
     AND tdate <  TRUNC(SYSDATE)

The advantage of this over using TRUNC on the date column is that it will utilize an index if it exists over tdate