等于（=）与LIKE的日期数据类型

First, I am aware that this question has been posted generally Equals(=) vs. LIKE. Here, I query about date type data on ORACLE database, I found the following, when I write select statment in this way:

首先，我知道这个问题一般都是等于（=）和LIKE。在这里，我在ORACLE数据库上查询日期类型数据，我发现以下内容，当我以这种方式编写select statment时：

SELECT ACCOUNT.ACCOUNT_ID, ACCOUNT.LAST_TRANSACTION_DATE
FROM ACCOUNT
WHERE ACCOUNT.LAST_TRANSACTION_DATE LIKE '30-JUL-07';

I get all rows I'm looking for. but when I use the sign equal = instead :

我得到了我正在寻找的所有行。但是当我使用sign = =时：

SELECT ACCOUNT.ACCOUNT_ID, ACCOUNT.LAST_TRANSACTION_DATE
FROM ACCOUNT
WHERE ACCOUNT.LAST_TRANSACTION_DATE = '30-JUL-07';

I get nothing even though nothing is different except the equal sign. Can I find any explanation for this please ?

即使除了等号之外没有什么不同，我什么也得不到。我可以找到任何解释吗？

3 个解决方案

#1

Assuming LAST_TRANSACTION_DATE is a DATE column (or TIMESTAMP) then both version are very bad practice.

假设LAST_TRANSACTION_DATE是DATE列（或TIMESTAMP），那么这两个版本都是非常糟糕的做法。

In both cases the DATE column will implicitly be converted to a character literal based on the current NLS settings. That means with different clients you will get different results.

在这两种情况下，DATE列将根据当前NLS设置隐式转换为字符文字。这意味着对于不同的客户，您将获得不同的结果。

When using date literals always use to_date() with(!) a format mask or use an ANSI date literal. That way you compare dates with dates not strings with strings. So for the equal comparison you should use:

使用日期文字时，始终使用带有（！）格式掩码的to_date（）或使用ANSI日期文字。这样你就可以将日期与日期而不是字符串与字符串进因此，对于平等比较，您应该使用：

LAST_TRANSACTION_DATE = to_date('30-JUL-07', 'dd-mon-yy')

Note that using 'MON' can still lead to errors with different NLS settings ('DEC' vs. 'DEZ' or 'MAR' vs. 'MRZ'). It is much less error prone using month numbers (and four digit years):

请注意，使用“MON”仍然会导致不同NLS设置的错误（'DEC'与'DEZ'或'MAR'与'MRZ'）。使用月份数（和四位数年份）更不容易出错：

LAST_TRANSACTION_DATE = to_date('30-07-2007', 'dd-mm-yyyy')

or using an ANSI date literal

或使用ANSI日期文字

LAST_TRANSACTION_DATE = DATE '2007-07-30'

Now the reason why the above query is very likely to return nothing is that in Oracle DATE columns include the time as well. The above date literals implicitly contain the time 00:00. If the time in the table is different (e.g. 19:54) then of course the dates are not equal.

现在，上述查询很可能不返回任何内容的原因是Oracle DATE列中也包含时间。上述日期文字隐含地包含时间00:00。如果表中的时间不同（例如19:54），那么日期当然不相等。

To workaround this problem you have different options:

要解决此问题，您有不同的选择：

1. use trunc() on the table column to "normalize" the time to 00:00 trunc(LAST_TRANSACTION_DATE) = DATE '2007-07-30 this will however prevent the usage of an index defined on LAST_TRANSACTION_DATE
2. 在表列上使用trunc（）将时间“规范化”为00:00 trunc（LAST_TRANSACTION_DATE）= DATE'2007-07-30但是这将阻止使用在LAST_TRANSACTION_DATE上定义的索引
3. use between
LAST_TRANSACTION_DATE between to_date('2007-07-30 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_date('2007-07-30 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
4. 在to_date（'2007-07-30 00:00:00'，'yyyy-mm-dd hh24：mi：ss'）和to_date（'2007-07-30 23:59:59'，'yyyy）之间的LAST_TRANSACTION_DATE之间使用-mm-dd hh24：mi：ss'）

The performance problem of the first solution could be worked around by creating an index on trunc(LAST_TRANSACTION_DATE) which could be used by that expression. But the expression LAST_TRANSACTION_DATE = '30-JUL-07' prevents an index usage as well because internally it's processed as to_char(LAST_TRANSACTION_DATE) = '30-JUL-07'

可以通过在trunc（LAST_TRANSACTION_DATE）上创建索引来解决第一个解决方案的性能问题，该索引可以由该表达式使用。但是表达式LAST_TRANSACTION_DATE = '30 -JUL-07'也会阻止索引使用，因为在内部它被处理为to_char（LAST_TRANSACTION_DATE）= '30 -JUL-07'

The important things to remember:

要记住的重要事项：

1. Never, ever rely on implicit data type conversion. It will give you problems at some point. Always compare the correct data types
2. 永远不要依赖隐式数据类型转换。它会在某些时候给你带来麻烦。始终比较正确的数据类型
3. Oracle DATE columns always contain a time which is part of the comparison rules.
4. Oracle DATE列始终包含时间，该时间是比较规则的一部分。

#2

You should not compare a date to a string directly. You rely on implicit conversions, the rules of which are difficult to remember.

您不应该直接将日期与字符串进行比较。你依赖于隐含的转换，其规则很难记住。

Furthermore, your choice of date format is not optimal: years have four digits (Y2K bug?), and not all languages have the seventh month of the year named JUL. You should use something like YYYY/MM/DD.

此外，您选择的日期格式不是最佳的：年份有四位数（Y2K错误？），而且并非所有语言都有一年中第七个月的JUL。你应该使用像YYYY / MM / DD这样的东西。

Finally, dates in Oracle are points in time precise to the second. All dates have a time component, even if it is 00:00:00. When you use the = operator, Oracle will compare the date and time for dates.

最后，Oracle中的日期是精确到秒的时间点。所有日期都有一个时间组件，即使它是00:00:00。当您使用=运算符时，Oracle将比较日期的日期和时间。

Here's a test case reproducing the behaviour you described:

这是一个再现您描述的行为的测试用例：

SQL> create table test_date (d date);

Table created

SQL> alter session set nls_date_format = 'DD-MON-RR';

Session altered

SQL> insert into test_date values
  2     (to_date ('2007/07/30 11:50:00', 'yyyy/mm/dd hh24:mi:ss'));

1 row inserted

SQL> select * from test_date where d = '30-JUL-07';

D
-----------

SQL> select * from test_date where d like '30-JUL-07';

D
-----------
30/07/2007

When you use the = operator, Oracle will convert the constant string 30-JUL-07 to a date and compare the value with the column, like this:

当您使用=运算符时，Oracle会将常量字符串30-JUL-07转换为日期并将该值与列进行比较，如下所示：

SQL> select * from test_date where d = to_date('30-JUL-07', 'DD-MON-RR');

D
-----------

When you use the LIKE operator, Oracle will convert the column to a string and compare it to the right-hand side, which is equivalent to:

当您使用LIKE运算符时，Oracle会将列转换为字符串并将其与右侧进行比较，这相当于：

SQL> select * from test_date where to_char(d, 'DD-MON-RR') like '30-JUL-07';

D
-----------
30/07/2007

Always compare dates to dates and strings to strings. Related question:

始终将日期与日期和字符串与字符串进行比较相关问题：

• How to correctly handle dates in queries constraints
• 如何正确处理查询约束中的日期

#3

The date field is not a string. Internally an implicit conversion is made to a string when you use =, which does not match anything because your string does not have the required amount of precision.

日期字段不是字符串。在内部使用=时会对字符串进行隐式转换，因为您的字符串没有所需的精度，所以它与任何内容都不匹配。

I'd have a guess that the LIKE statement behaves somewhat differently with a date field, causing implicit wildcards to be used in the comparison that eliminates the requirement for any precision. Essentially, your LIKE works like this:

我猜测LIKE语句与日期字段的行为有些不同，导致在比较中使用隐式通配符，从而消除了对任何精度的要求。基本上，你的LIKE的工作原理如下：

SELECT ACCOUNT.ACCOUNT_ID, ACCOUNT.LAST_TRANSACTION_DATE
FROM ACCOUNT
WHERE ACCOUNT.LAST_TRANSACTION_DATE BETWEEN DATE('30-JUL-07 00:00:00.00000+00:00') AND DATE('30-JUL-07 23:59:59.99999+00:00');