According to the Oracle documentation, you can use quoted dates to qualify
records in a WHERE clause where the column data type is DATE. We have found
that this behavior is not always correct, so we changed our programs
to use TO_CHAR(field,'DD-MON-YY'), which make no use of the index (of course
assuming the index contain the date field).
Our Oracle versions are:
SQL*Plus: Version 3.0.9.1.2
ORACLE RDBMS V6.0.33.1.1
PL/SQL V1.0.32.3.1
Can somebody explain why DATE fields have such a behavior:
- Table description:
SQL> desc sample_table;
Name Null? Type
------------------------------- -------- ----
CODE_OUVRAGE NOT NULL CHAR(10)
DATE_MESURE NOT NULL DATE
TYPE_LECTURE CHAR(1)
INSPECTEUR CHAR(9)
2) Sample query #1:
SQL> r
1 SELECT code_ouvrage, date_mesure
2 FROM sample_table
3 WHERE code_ouvrage = 'CD-00'
4* AND date_mesure = '22-SEP-92' /* Should work, according to doc.
no rows selected
3) Sample query #2:
SQL> r
1 SELECT code_ouvrage, date_mesure
2 FROM sample_table
3 WHERE code_ouvrage = 'CD-00'
4 AND date_mesure = /* Should work, but useless
5* TO_DATE('22-SEP-92','DD-MON-YY')
no rows selected
4) Sample query #3:
SQL> r
1 SELECT code_ouvrage, date_mesure
2 FROM sample_table
3 WHERE code_ouvrage = 'CD-00'
4* AND TO_CHAR(date_mesure,'DD-MON-YY') = '22-SEP-92'
/* Work, but make no use of the index and make an unnecessary
/* data type conversion
CODE_OUVRA DATE_MESU
---------- ---------
CD-00 22-SEP-92
CD-00 22-SEP-92
CD-00 22-SEP-92
