Question

This question already has answers here:

How to correctly handle dates in queries constraints (4 answers)

Closed 8 years ago.

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.

数据库有日均值的记录。

页: 1 * 摘自WHERE表:daterecord=_date(03-Mar-2010)

它不是归还任何记录,而是如果我把问答改为我。

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 )

Answer 3

http://www.techonthenet.com/oracle/Functions/trunc_date.php” rel=“nofollow”

例如:

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

友情链接