如何在Oracle中比较日期？

Question

如何在Oracle中比较日期？

5

我遇到了一个问题，本以为很简单，但是搜索引擎没有提供有效的帮助。可能是我不太擅长搜索相关信息。

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

它告诉我使用了无效的标识符。我已经尝试使用MON代替MMM，但这并没有帮助。

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

- Matt Farrell

5

为什么要将日期转换为字符串进行比较？为什么不直接使用 WHERE ORDER_DATE = '2000-06-07' 呢？ - Siyual

我不认为“format”是一个函数，就像你的代码所示。你想要使用TO_CHAR。 - Veverke

2

order_date的数据类型是什么？如果是DATE类型，则需要使用where order_date = to_date('07/06/2000', 'dd/mm/yyyy')。这假定订单日期列中没有存储任何时间元素（即所有存储都在午夜时刻）。 - Boneist

2

你在Oracle手册的哪里找到了format()函数？ - user330315

2

你为什么接受了一个完全错误的答案？ - Lalit Kumar B

1

@Siyual '2000-06-07' 是一个字符串，而不是日期。你的左侧是正确的，但是你必须在右侧使用 TO_DATE 或 ANSI 日期字面量。 - Lalit Kumar B

4个回答

0

试试这个：

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

- rockerchain

-1

我不认识FORMAT，它不是Oracle函数。

我想你的意思是TO_CHAR。

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

- Veverke

1

把右边转换会更好吗？（如果ORDER_DATE已经建立索引...） - jarlh

1

然而，更大的问题是为什么要这样做？为什么不直接将其与标准日期格式进行比较呢？ - user1919238

2

这完全是错误的，应该永远避免。绝不要将字符串与日期进行比较。始终使用TO_DATE或ANSI日期文字来明确地将字符串转换为日期。 - Lalit Kumar B

在 TO_CHAR(dateField, '...dateFormat...') = 'dateString' 中，隐式转换发生在哪里？你在这里比较了两个字符串。 - Veverke

1

@Veverke 首先，ANSI日期字面量是NLS独立的，而且它没有时间部分。其次，TO_DATE很容易受到NLS设置的影响，这一点我已经提到了。你确定你在建议Oracle而不是SQL Server的东西吗？ - Lalit Kumar B

显示剩余7条评论

-2

尝试使用to_char(order_date, 'DD-MON-YYYY')

- vins

3

更好的方式是在参数中使用 to_date 而不是在列中使用 to_char。 - Stawros

2

最好使用to_char(order_date, 'DD-MM-YYYY') = '07-06-2000'，以便独立于会话NLS_DATE_LANGUAGE。 - Wernfried Domscheit

1

@Wernfried 我真的很想看到证据。我从未在现实世界中听说过这样的做法。 - Lalit Kumar B

3

@Lalit Kumar B：比较包含日期部分的字符串是没有问题的。这通常甚至很容易阅读（例如 to_char(mydate, 'yyyymm') = '201510'）。但是，Wernfried的评论很重要：在处理月份名称时应指定日期语言。因此，这是正确的答案，只是可以更好些 :-) - Thorsten Kettner

1

@ThorstenKettner 您忽略了数据类型最重要的一点。在处理日期时，尤其是比较/算术运算时，应该将日期保持不变。请记住，TO_CHAR 仅用于显示目的而不应在执行日期运算时使用。 - Lalit Kumar B

显示剩余7条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Lalit Kumar B · Accepted Answer

与您的日期使用有多个问题：

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

FORMAT不是Oracle支持的内置函数。
永远不要将字符串与日期进行比较。您可能会很幸运，但是您强制Oracle根据您的区域设置进行隐式数据类型转换。您必须避免它。始终使用TO_DATE将字符串明确转换为日期。

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

当您处理仅涉及日期而不涉及时间部分时，最好使用ANSI DATE Literal。

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

在文档中了解更多DateTime文字的信息。

更新

我认为添加一些关于日期的信息可能会有所帮助。

Oracle不会以您看到的格式存储日期。它在7个字节中以专有格式内部存储，每个字节存储datetime值的不同组件。

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-Tree索引。现在，由于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'。