8

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.

Shoe
  • 74,840
  • 36
  • 166
  • 272
Infotechie
  • 1,653
  • 6
  • 23
  • 35

3 Answers3

16

Yes you can use TRUNC function.

SELECT * 
  FROM table 
 WHERE TRUNC(table.daterecord) = TO_DATE('03-Mar-2010', 'DD-MON-RRRR')
Chandu
  • 81,493
  • 19
  • 133
  • 134
8

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')
Community
  • 1
  • 1
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • +1, I like this due to ability to use index (vs trunc) – tbone Apr 04 '11 at 19:29
  • +1 for your answer in the other question :) – Ronnis Apr 04 '11 at 21:15
  • Given that you know this is a duplicate question you should have voted to close the question, rather than duplicating your answer. It's not like you really need the points. – APC Apr 05 '11 at 06:23
  • 2
    @APC: I felt that the questions were sufficiently distinct. My answer is a duplicate though (same principle applied to another case), maybe I should have pointed to the question in a comment rather than an answer. – Vincent Malgrat Apr 05 '11 at 09:03
5

you can use TRUNC

For example : 

SELECT * 
  FROM table 
 WHERE TRUNC(table.daterecord) = TO_DATE('03-Mar-2010', 'DD-MON-RRRR')
Tufan Barış Yıldırım
  • 3,952
  • 26
  • 36