如何仅从 Oracle 中的日期字段中提取日期值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5542080/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to extract only date value from date field in Oracle?
提问by Infotechie
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.
我是 Oracle 数据库的新手。我对 Oracle 中的日期时间概念知之甚少。我面临的问题是检索在特定日期输入的记录。但是当我在数据库上执行 SQL 查询时，它返回零记录。
Database has date field which contains records with both datetime value.
数据库具有日期字段，其中包含具有日期时间值的记录。
SQL Query: SELECT * FROM table WHERE table.daterecord = to_date(03-Mar-2010)
SQL查询： 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.
它会返回一些记录。上述差异是因为时间。如何从日期中提取时间值。我可以为此使用 trunc 函数吗？在此先感谢您的宝贵建议。
回答by Chandu
Yes you can use TRUNC function.
是的，您可以使用 TRUNC 功能。
SELECT *
FROM table
WHERE TRUNC(table.daterecord) = TO_DATE('03-Mar-2010', 'DD-MON-RRRR')
回答by Vincent Malgrat
see this SO for suggestions of "How to correctly handle dates in queries constraints"?
请参阅此 SO 以获取有关“如何正确处理查询约束中的日期”的建议？
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')
