Re: Date Comparison

"John P. Higgins" <jh33378_at_deere.com> wrote:

>Steve Dirschel wrote:
>>
>> Could someone please explain this to me:
>>
>> Create table dummy (a date);
>>
>> Insert into dummy values (sysdate);
>> Insert into dummy values (sysdate);
>> Commit;
>>
>> Select * from dummy;
>> 10-DEC-96
>> 10-DEC-96
>>
>> Select * from dummy where a = '10-DEC-96'
>>
>> 0 rows selected
>>
>> Select * from dummy where a > '10-DEC-96'
>> 10-DEC-96
>> 10-DEC-96
>> 2 rows selected
>>
>> Why do I not get 2 rows returned when I try the select * from dummy
>> where a = '10-DEC-96' ? I realize I can do a "less than 11-DEC-96 and
>> greater than 10-DEC-96" but it seems like I shouldn't have to do all
>> of that typing.
>>
>> Thanks in advanceOracle date columns include both date, hour, minute and second. The
>SYSDATE function returns the current date and time, so each insert has a
>unique date and time.
 

>You are not seeing this because the default date display does not
>include the time components. If you did:
>SELECT TO_CHAR(a,'DD-MON-YY HH24:MI:SS) from dummy;
>you would see the whole value.
 

>--
>John P. Higgins Voice: (309)765-4481
>Deere & Company Fax: (309)765-5168
>John Deere Road Internet: jh33378_at_deere.com
>Moline, IL 61265 Opinions: My Own

Okay, the answer is really easy. Thing of time as a non-discreet counting system. This means that you can never say request the data where a date_time field IS EQUAL TO a specific time. You can however say where date_time field IS (>,>=,<,<=,!=).

The only reasonable way is to convert the date_time field into a discreet value. This can be done in a number of ways. The most straight forward is to use TRUNC.
eg
 SELECT *
   FROM dummy
  WHERE TRUNC( a ) = TO_DATE( '10-DEC-1996', 'DD-MON-YYYY' );

Hope this clarifies the question. As much as I like to slag Oracle off for their f.u., this is not Oracle's problem, but a side effect of dealing with Discreet/Non-Discreet counting systems.

take care
geoff
an Ozzie lost in the wilderness
#include <standard_disclaimer.h>
The thoughts are someone elses, just the words are mine Jif/Geoff/Goff (an aussie threw & thrwe) Received on Fri Dec 13 1996 - 00:00:00 CST