sql - 如何比较Oracle中的日期？

Question

我遇到了我认为是一个简单问题的问题，但谷歌搜索并没有帮助很大的负载。可能我不擅长我正在寻找的下界。

SELECT ORDER_NUMB, CUSTOMER_NUMB, ORDER_DATE
FROM ORDERS
WHERE FORMAT(ORDER_DATE, 'DD-MMM-YYYY') = '07-JUN-2000';

它告诉我我使用了一个无效的标识符。我曾尝试使用 MON 而不是 MMM ，但这也无济于事。

不确定它是否有任何区别，但我使用的是 Oracle SQL Developer。

Answer 1

有多个与您的 DATE 使用相关的问题:

WHERE FORMAT(ORDER_DATE, 'DD-MMM-YYYY') = '07-JUN-2000';

格式 不是 Oracle 支持的内置函数。

永远不要将 STRING 与 DATE 进行比较。您可能只是很幸运，但是，您强制 Oracle 根据您的 特定于语言环境的 NLS 设置 执行 隐式数据类型转换 。你必须避免它。始终使用 TO_DATE 将字符串显式转换为日期。

WHERE ORDER_DATE = TO_DATE('07-JUN-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH');

3. When you are dealing only with date without the time portion, then better use the ANSI DATE Literal.

WHERE ORDER_DATE = DATE '2000-06-07';

Read more about DateTime literals in documentation.

---

Update

It think it would be helpful to add some more information about 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.

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  --    "                   "

记住，

To display                      : Use TO_CHAR
Any date arithmetic/comparison  : Use TO_DATE

性能瓶颈:

假设您在日期列上有一个常规的 B 树索引 。现在，由于 TO_CHAR 函数，以下 过滤谓词 将永远不会使用索引:

WHERE TO_CHAR(ORDER_DATE, 'DD-MM-YYYY') = '07-06-2000';

因此，在上述查询中使用 TO_CHAR 是完全没有意义的，因为它不比较日期，也没有提供良好的性能。

正确方法:

进行日期比较的正确方法是:

WHERE ORDER_DATE = TO_DATE('07-JUN-2000','DD-MON-YYYY','NLS_DATE_LANGUAGE=ENGLISH');

它将使用 ORDER_DATE 列上的索引，因此在性能方面会好得多。此外，它正在比较日期而不是字符串。

正如我已经说过的，当您的日期中没有时间元素时，您可以使用 ANSI 日期文字 ，它与 NLS 无关，并且不需要编码。

WHERE ORDER_DATE = DATE '2000-06-07';

它使用固定格式 'YYYY-MM-DD' 。