Equals(=) vs. LIKE for date data type

Question

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:

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 :

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 ?

Answer 1

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

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.

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:

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):

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

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. 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 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'

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. Oracle DATE columns always contain a time which is part of the comparison rules.

Answer 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.

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.

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:

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:

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

Answer 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:

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