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

Solution 1:

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));

Solution 2:

Convert your date column to the correct format and compare:

SELECT * From my_table WHERE to_char(my_table.my_date_col,'MM/dd/yyyy') = '8/3/2010'

This part

to_char(my_table.my_date_col,'MM/dd/yyyy')

Will result in string '8/3/2010'

Solution 3:

Personally, I usually go with:

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

Solution 4:

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

Solution 5:

trunc(my_date,'DD') will give you just the date and not the time in Oracle.