Re: Problems in DATE equality

Roberto Finelli wrote:
>
> Something went wrong in last times, because I can't do something very
> usual, like DATE equalitity tests.
>
> I have got a DATE field in a table : when I try to select with an EQUAL
> condition in the WHERE on this field, like this :
> SELECT * from TABLE where DATE_FIELD = TO_DATE('16051997','DDMMYYYY')
> I don't get any records !
>
> Note that the SELECT returns the right answer if I put it like this :
> SELECT * from TABLE where DATE_FIELD BETWEEN
> TO_DATE('15051997','DDMMYYYY') AND TO_DATE('17051997','DDMMYYYY')
> so it seems that the problem is with equality only.
>
> I have tried to select with the time too, like this :
> SELECT * from TABLE where DATE_FIELD =
> TO_DATE('16051997045715','DDMMYYYYHHMISS)
> but also this didn't succeed.
>
> Could someone give an explanation ?
> Thanks

It is almost certain that your database date_field includes a time element.
By using a mask for your literal which does not reference the time, it is assumed to have time 00:00:00.

Note that times stored as sysdate *always* include the time portion. If you do not want this, make sure you store trunc(sysdate) rather than sysdate.

If you use "where trunc(date_field) = to_date('16051997','DDMMYYYY') you will probably get the results you expect. (This, of course, prevents the use of an index on date_field)

If you *do* want to include the time in the stored field, *and* you want to be able to use any index there may be on the date_field, your comparisons will have to be of the form you used in your example.

Hope this helps.

Chrysalis. Received on Mon May 19 1997 - 00:00:00 CDT