Re: Date Problems with Where clause - Oracle 7.3.2

>I am trying to write a query to return all rows where my_date = a date I
>pass. However, I seem to be running into a problem with precision on
>dates.
>
>Here's an example:
>
>select * from my_table where my_date = to_date('07/08/1998',"MM/DD/YYYY)
>
>Even though I have a row in this table with the same date, it will not
>be returned. My guess is that because this date also contains the time
>3:00 AM, we don't have a perfect match. So far, I haven't figured out
>how to limit the precision to the day. The only two workarounds I can
>think of are to do these:
>
>1. select * from my_table where to_char(my_date,'MM/DD/YYYY') =
>'07/08/1998'
>2. select * from my_table where my_date between to_date('07/08/1998
>12:00 AM','MM/DD/YYYY HH:MI AM') and to_date('07/08/1998 12:59
>PM','MM/DD/YYYY HH:MI AM')
>
>Neither of these seems like a very elegant solution. Does anyone have a
>better one?
>
>Thanks,
>Ben Geyer
>Caterpillar, Inc.
>geyer_ben_a_at_cat.com
>
>

As others have suggested, use the TRUNC function. As others have also pointed out, this will preclude the use of any index. However, if you only need the date, you can store only the date, so you can still use the index. To store just the date, create a db trigger on the table that does this:

:new.my_date :=trunc(:new.my_date);

Now when you query against the date, an index can still be used. That is,

select ... from ... where
my_date = trunc(sysdate)

One word of caution - Oracle automatically converts data elements to other types. While investigating my code, I discovered that the db date field was getting converted to a varchar and compared with sysdate, rather than sysdate being converted to a date and compared to the db date field. I'm sure there is something somewhere that explains the rules behind this, but it was easier and faster to just convert it to a date...

where my_date = trunc(to_date(sysdate))

Double check on a simple query using explain_plan and make sure - it may depend on the version of the DB you are using.

Dan Hekimian-Williams

will still use the index on my_date. Received on Thu Jul 09 1998 - 21:38:55 CDT