Re: Date Problems with Where clause - Oracle 7.3.2

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 08 Jul 1998 19:34:44 GMT
Message-ID: <35a9c88f.24012858@192.86.155.100>

A copy of this was sent to Ben Geyer <geyer_ben_a_at_cat.com> (if that email address didn't require changing) On Wed, 08 Jul 1998 13:41:44 -0500, you wrote:

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

one way is:

select * from my_table where TRUNC(my_date,'d') = to_date(....)

That will preclude an index on my_date from being used however so if you have and want to use the index you can:

select * from my_table where my_date between to_date(...) and to_date(...)+1;

that can still use an index range scan to find it... (it'll also get things that happened exactly at Midnight on the next day so you could use:

to_date('05-jul-98')+1-1/24/60/60

instead of to_date(...)+1. 1/24/60/60 is 1 second....

>Thanks,
>Ben Geyer
>Caterpillar, Inc.
>geyer_ben_a_at_cat.com
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jul 08 1998 - 14:34:44 CDT