oracle 如何在oracle中的where子句上写日期条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42013494/
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 write date condition on where clause in oracle
提问by Shakeer Hussain
I have data in the date column as below.
我在日期列中有数据，如下所示。
reportDate
报告日期
21-Jan-17 02-FEB-17
17 年 1 月 21 日 17 年 2 月 2 日
I want to write a query to fetch data for 01/21/2017?
我想写一个查询来获取 01/21/2017 的数据？
Below query not working in Oracle.
以下查询在 Oracle 中不起作用。
SELECT * FROM tablename where reportDate=to_date('01/21/2017','mm/dd/yyyy')
SELECT * FROM tablename where reportDate=to_date('01/21/2017','mm/dd/yyyy')
回答by mathguy
What is the data type of reportDate? It may be DATE or VARCHAR2 and there is no way to know by just looking at it.
的数据类型是reportDate什么？它可能是 DATE 或 VARCHAR2 并且无法通过查看它来知道。
Run describe table_name(where table_nameis the name of the table that contains this column) and see what it says.
运行describe table_name（其中table_name包含此列的表的名称）并查看其内容。
If it's a VARCHAR2 then you need to convert it to a date as well. Use the proper format model: 'dd-Mon-rr'.
如果它是 VARCHAR2，那么您还需要将其转换为日期。使用正确的格式模型：'dd-Mon-rr'.
If it's DATE, it is possible it has time-of-day component; you could apply trunc()to it, but it is better to avoid calling functions on your columns if you can avoid it, for speed. In this case (if it's really DATE data type) the wherecondition should be
如果是 DATE，则它可能具有时间组件；您可以申请trunc()它，但如果可以避免，最好避免在列上调用函数，以提高速度。在这种情况下（如果它真的是 DATE 数据类型）where条件应该是
where report_date >= to_date('01/21/2017','mm/dd/yyyy')
and report_date < to_date('01/21/2017','mm/dd/yyyy') + 1
Note that the date on the right-hand side can also be written, better, as
请注意，右侧的日期也可以更好地写成
date '2017-01-21'
(this is the ANSI standard date literal, which requires the key word dateand exactlythe format shown, since it doesn't use a format model; use -as separator and the format yyyy-mm-dd.)
（这是ANSI标准日期文字，这需要键字date和正好所示的格式，因为它不使用的格式模型;使用-作为分离器和格式yyyy-mm-dd）。
回答by Ashwini
The query should be something like this
查询应该是这样的
SELECT *
FROM table_name
WHERE TRUNC(column_name) = TO_DATE('21-JAN-17', 'DD-MON-RR');
The TRUNCfunction returns a date value specific to that column.
该TRUNC函数返回特定于该列的日期值。
The o/p which I got when I executed in sqldeveloper https://i.stack.imgur.com/blDCw.png
我在 sqldeveloper 中执行时得到的 o/p https://i.stack.imgur.com/blDCw.png
