Re: Problem with date fields

From: L. Carl Pedersen <carl.pedersen_at_dartmouth.edu>
Date: 29 Sep 92 23:32:09 GMT
Message-ID: <carl.pedersen-290992192618_at_kip-sn-49.dartmouth.edu>


In article <shamel.717774532_at_tdsb-s>, shamel_at_mais.hydro.qc.ca (Stephane Hamel) wrote:
>
>
> 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:
>
> 1) 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
> --
> Stephane Hamel (SHamel_at_Mais.Hydro.Qc.Ca) | AutoControl Inc.
> Senior Programmer-Analyst | Montreal, Quebec, Canada

Your DATE fields must include the time. If you want to store just the date (with a time set to midnight, 00:00:00), store TRUNC(SYSDATE) instead of SYSDATE. You can also compare to trunc(date_mesure), but as you point out this will defeat the use of any index.

[Anybody know what the standard procedure is for setting up a FAQ list? I might be willing to maintain such a thing. I assume mine will be one of numerous replies.] Received on Wed Sep 30 1992 - 00:32:09 CET

Original text of this message