How to extract only date value from date field in Oracle?

Question

I am new to Oracle database. I am not very good at date and time concepts in Oracle. The problem I am facing is retrieving the entries that are entered on a specific date. But when I execute the SQL query in the database, it returns null records.

The database has a date field that contains records with both date and time values.

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

It does not return any records, but if I change my request to

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

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

Answer 1

Yes, you can use the TRUNC function.

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

Answer 2

see this SO for suggestions. How to handle dates in query restrictions "?

In addition to the answers already provided, I would suggest using a range as it is easier to index:

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

Answer 3

you can use TRUNC

For example:

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