Equals (=) vs. LIKE for date data type

Question

First, I know that this question was posted as a whole by Equals (=) vs LIKE . Here I am asking for date type data in the ORACLE database, I found the following when I write select statment as follows:

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

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

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

I get nothing, although nothing is different from the equal sign. Can I find an explanation for this, please?

Answer 1

Assuming LAST_TRANSACTION_DATE is a DATE (or TIMESTAMP ) column, both versions are very bad.

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

When using date literals, always use to_date() with a (!) Format mask or use an ANSI date literal. This way you compare dates with dates, not strings with strings. Therefore, for equal comparison, you should use:

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

Note that using “MON” may still lead to errors with different NLS settings ( 'DEC' versus 'DEZ' or 'MAR' versus 'MRZ' ). Much less prone to errors using month numbers (and four-digit years):

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

or using an ANSI date literal

 LAST_TRANSACTION_DATE = DATE '2007-07-30' 

Now the reason why the above query most likely returns nothing is because the Oracle DATE columns also indicate the time. The above date literals implicitly contain the time 00:00 . If the time in the table is different (for example, 19:54 ), then, of course, the dates are not equal.

To get around this problem, you have different options:

• use trunc() in the column of the table to "normalize" the time until 00:00 trunc(LAST_TRANSACTION_DATE) = DATE '2007-07-30 this, however, will prevent the use of the index defined on LAST_TRANSACTION_DATE
• 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')

The performance problem of the first solution can be solved by creating an index on trunc(LAST_TRANSACTION_DATE) , which can be used by this expression. But the expression LAST_TRANSACTION_DATE = '30-JUL-07' prevents the use of the index, because internally it is treated as to_char(LAST_TRANSACTION_DATE) = '30-JUL-07'

Important to remember:

• Never, never rely on implicit data type conversion. At some point this will cause problems. Always compare the correct data types.
• Oracle DATE columns always contain time, which is part of the comparison rules.

Answer 2

You should not compare the date with the string directly. You rely on implicit conversions whose rules are hard to remember.

In addition, your date format is not optimal: years have four digits (Y2K error?), And not all languages ​​have a seventh month of the year named JUL . You should use something like YYYY/MM/DD .

Finally, dates in Oracle are exact points in 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.

Here's a test case that reproduces the behavior 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 converts the constant string 30-JUL-07 to a date and compares the value with the column, for example:

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

When you use the LIKE operator, Oracle converts the column to a row and compares it with the right side, which is equivalent to:

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

Always compare dates with dates and strings with strings. Related Question:

• How to properly handle dates in query restrictions

Answer 3

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

I would suggest that the LIKE operator behaves somewhat differently with a date field, resulting in the use of implicit wildcards in the comparison, eliminating the need for any precision. Essentially, your LIKE works as follows:

 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');