Use the trunc function on the my_date column.
--
Matt Brennan
SQL*Tools Specialist
GERS Retail Systems
9725-C Scranton Road
San Diego, California 92121
1-800-854-2263
mbrennan_at_gers.com
(Original email address is spam-blocked.)
Ben Geyer <geyer_ben_a_at_cat.com> wrote in article
<35A3BD68.97805462_at_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 - 17:20:56 CDT
