Date Problems with Where clause - Oracle 7.3.2

From: Ben Geyer <geyer_ben_a_at_cat.com>
Date: Wed, 08 Jul 1998 13:41:44 -0500
Message-ID: <35A3BD68.97805462@cat.com>

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 Received on Wed Jul 08 1998 - 13:41:44 CDT