From: sstephen@us.oracle.com
Newsgroups: comp.databases.oracle
Subject: Re: Problem with date fields
Date: 1 Oct 92 01:21:05 GMT
Organization: Oracle Corporation, USA
X-Disclaimer: This message was written by an unauthenticated user
              at Oracle Corporation.  The opinions expressed are those
              of the user and not necessarily those of Oracle.

In article <shamel.717774532@tdsb-s>, shamel@mais.hydro.qc.ca (Stephane Hamel) writes:
> 
> 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:
> 

Carl Pedersen has already replied to this message, but here is a way you can
get it to do what you MEAN *and* use an index.

> 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

date_measure apparently has a timestamp that is not 00:00 midnight.  The string
'22-SEP-92', get converted to a date structure which represents 22-SEP-1992
00:00.

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

The TO_DATE you descibed will do what was already implicit in query #1.

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

Now, you have converted date_measure to a date, truncating the timestamp and
making them equivalent.  You could have done the same thing with :

TO_CHAR(date_mesure) = '22-SEP-92'
TRUNC(date_mesure,'D') = '22-SEP-92' /* 'D' means to the day */
TRUNC(date_mesure) = '22-SEP-92'     /* or ROUND or FLOOR */

Note that using TO_CHAR will invalidate the usefulness of a date, i.e.,
'04-JUL-76' > '01-DEC-92'!!

In order to use an index, try :

... AND date_mesure BETWEEN '22-SEP-92' AND '23-SEP-92'

(which means any timestamp during the day of 22-SEP-92), and this will do
a range scan on your index.


> --
>  Stephane Hamel (SHamel@Mais.Hydro.Qc.Ca) | AutoControl Inc.
>  Senior Programmer-Analyst                | Montreal, Quebec, Canada


Scott Stephens -- and his own private rantings.
Newsgroups: comp.databases.oracle