Select from table by knowing only date without time (ORACLE)

Question

Greeting People,

I'm trying to retrieve records from table by knowing the date in column contains date and time.

Suppose I have table called t1 which contains only two column name and date respectively.

The data stored in column date like this 8/3/2010 12:34:20 PM.

I want to retrieve this record by this query for example (note I don't put the time):

Select * From t1 Where date="8/3/2010"

This query give me nothing !

How can i retrieve date by knowing only date without the time?

Answer 1

You could use the between function to get all records between 2010-08-03 00:00:00:000 AND 2010-08-03 23:59:59:000

Answer 2

DATE is a reserved keyword in Oracle, so I'm using column-name your_date instead.

If you have an index on your_date, I would use

WHERE your_date >= TO_DATE('2010-08-03', 'YYYY-MM-DD')
  AND your_date <  TO_DATE('2010-08-04', 'YYYY-MM-DD')

or BETWEEN:

WHERE your_date BETWEEN TO_DATE('2010-08-03', 'YYYY-MM-DD')
                    AND TO_DATE('2010-08-03 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

If there is no index or if there are not too many records

WHERE TRUNC(your_date) = TO_DATE('2010-08-03', 'YYYY-MM-DD')

should be sufficient. TRUNC without parameter removes hours, minutes and seconds from a DATE.

---

If performance really matters, consider putting a Function Based Index on that column:

CREATE INDEX trunc_date_idx ON t1(TRUNC(your_date));

Answer 3

Try the following way.

Select * from t1 where date(col_name)="8/3/2010" 

Answer 4

Personally, I usually go with:

select * 
from   t1
where  date between trunc( :somedate )          -- 00:00:00
            and     trunc( :somedate ) + .99999 -- 23:59:59