How to extract only date value from date field in Oracle? [duplicate]

Question

I am new to Oracle database.I dont have much knowledge about date-time concepts in Oracle. The problem i am facing is to retrieve records which are entered on a particular date.But when i am executing SQL query on database it returns zero records.

Database has date field which contains records with both datetime value.

SQL Query: SELECT * FROM table WHERE table.daterecord = to_date(03-Mar-2010)

It is not returning any record but if i change my query to

SELECT * FROM table WHERE table.daterecord > to_date(04-Mar-2010)

It will return some records. The above difference is because of time.How can i extract time value from date. Can I use trunc function for this? Thanks in advance for your valuable suggestions.

Answer 1

Yes you can use TRUNC function.

SELECT * 
  FROM table 
 WHERE TRUNC(table.daterecord) = TO_DATE('03-Mar-2010', 'DD-MON-RRRR')

Answer 2

see this SO for suggestions of "How to correctly handle dates in queries constraints"?

In addition to the answers already provided, I would suggest using a range since this is more easily indexable:

SELECT * 
  FROM table 
 WHERE table.daterecord >= TO_DATE('03-Mar-2010', 'DD-MON-RRRR')
   AND table.daterecord < TO_DATE('04-Mar-2010', 'DD-MON-RRRR')