SQL 如何比较Oracle中的日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32972250/
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 compare date in Oracle?
提问by Matt Farrell
I'm having issues with what I assumed would be a simple problem, but googling isn't helping a great load. Possibly I'm bad at what I am searching for nether the less.
我遇到了我认为是一个简单问题的问题，但谷歌搜索并没有帮助很大的负载。可能我不擅长我正在寻找的下界。
SELECT ORDER_NUMB, CUSTOMER_NUMB, ORDER_DATE
FROM ORDERS
WHERE FORMAT(ORDER_DATE, 'DD-MMM-YYYY') = '07-JUN-2000';
It tells me I am using an invalid identifier. I have tried using MONinstead of MMM, but that doesn't help either.
它告诉我我使用了一个无效的标识符。我曾尝试使用MON而不是MMM，但这也无济于事。
Unsure if it makes any difference but I am using Oracle SQL Developer.
不确定它是否有任何区别，但我使用的是 Oracle SQL Developer。
回答by Lalit Kumar B
There are multiple issues related to your DATEusage:
有多个与您的DATE使用相关的问题：
WHERE FORMAT(ORDER_DATE, 'DD-MMM-YYYY') = '07-JUN-2000';
WHERE FORMAT(ORDER_DATE, 'DD-MMM-YYYY') = '07-JUN-2000';
- FORMATis not an Oraclesupported built-in function.
- Never ever compare a STRINGwith DATE. You might just be lucky, however, you force Oracle to do an implicit data type conversionbased on your locale-specific NLS settings. You must avoid it. Always use TO_DATEto explicitly convert string to date.
- FORMAT不是Oracle支持的内置函数。
- 永远不要将STRING与DATE进行比较。您可能只是幸运，但是，您强制 Oracle根据特定于区域设置的 NLS 设置进行隐式数据类型转换。你必须避免它。始终使用TO_DATE将字符串显式转换为日期。
WHERE ORDER_DATE = TO_DATE('07-JUN-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH');
- When you are dealing only with date without the time portion, then better use the ANSI DATE Literal.
- 当您只处理没有时间部分的日期时，最好使用ANSI DATE Literal。
WHERE ORDER_DATE = DATE '2000-06-07';
Read more about DateTime literals in documentation.
在文档中阅读有关 DateTime 文字的更多信息。
Update
更新
It think it would be helpful to add some more information about DATE.
它认为添加一些有关DATE 的更多信息会有所帮助。
Oracle does not store dates in the format you see. It stores it internally in a proprietary format in 7 bytes with each byte storing different components of the datetime value.
Oracle 不会以您看到的格式存储日期。它以专有格式在内部以 7 个字节存储它，每个字节存储日期时间值的不同组成部分。
BYTE Meaning
---- -------
1 Century -- stored in excess-100 notation
2 Year -- " "
3 Month -- stored in 0 base notation
4 Day -- " "
5 Hour -- stored in excess-1 notation
6 Minute -- " "
7 Second -- " "
Remember,
记住，
To display : Use TO_CHAR
Any date arithmetic/comparison : Use TO_DATE
Performance Bottleneck:
性能瓶颈：
Let's say you have a regular B-Tree indexon a date column. now, the following filter predicatewill never use the index due to TO_CHAR function:
假设您在日期列上有一个常规的B 树索引。现在，由于 TO_CHAR 函数，以下过滤谓词将永远不会使用索引：
WHERE TO_CHAR(ORDER_DATE, 'DD-MM-YYYY') = '07-06-2000';
So, the use of TO_CHAR in above query is completely meaningless as it does not compare dates, nor does it delivers good performance.
因此，在上述查询中使用 TO_CHAR 完全没有意义，因为它不比较日期，也没有提供良好的性能。
Correct method:
正确方法：
The correct way to do the date comparison is:
进行日期比较的正确方法是：
WHERE ORDER_DATE = TO_DATE('07-JUN-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH');
It will use the index on the ORDER_DATE column, so it will much better in terms of performance. Also, it is comparing dates and not strings.
它将使用 ORDER_DATE 列上的索引，因此在性能方面会好得多。此外，它正在比较日期而不是字符串。
As I already said, when you do not have the timeelement in your date, then you could use ANSI date literalwhich is NLS independent and also less to code.
正如我已经说过的，当您的日期中没有时间元素时，您可以使用ANSI 日期文字，它与 NLS 无关，并且不需要编码。
WHERE ORDER_DATE = DATE '2000-06-07';
It uses a fixed format 'YYYY-MM-DD'.
它使用固定格式'YYYY-MM-DD'。
回答by rockerchain
try this:
尝试这个：
SELECT ORDER_NUMB, CUSTOMER_NUMB, ORDER_DATE
FROM ORDERS
WHERE trunc(to_date(ORDER_DATE, 'DD-MMM-YYYY')) = trunc(to_date('07-JUN-2000'));
回答by Veverke
I do not recognize FORMATas an oracle function.
我不承认FORMAT是一个 oracle 函数。
I think you meant TO_CHAR.
我想你的意思是TO_CHAR。
SELECT ORDER_NUMB, CUSTOMER_NUMB, ORDER_DATE
FROM ORDERS
WHERE TO_CHAR(ORDER_DATE, 'DD-MMM-YYYY') = '07-JUN-2000';
回答by vins
try to_char(order_date, 'DD-MON-YYYY')
尝试 to_char(order_date, 'DD-MON-YYYY')
