- You should use to_date and not rely on the nls_date_format defaulting to
something (especially 2 digit years, remember Y2K) Instead you could do:
select * from table where
date BETWEEN TO_DATE('08/05/2002', 'MM/DD/YYYY')
AND TO_DATE('08/06/2002', 'MM/DD/YYYY')
It is possible to trunc(date) but then you are going to force a full table
scan unless you create a function based index on the table.
Jim
"Ralph Snart" <snart_at_nospam.com> wrote in message
news:slrnal5uuj.1iac.snart_at_cluttered.com...
>
> ok this is really dumb, and i would think it's also a faq,
> although all the faq references i have seen only deal
> with inserts and not selects.
>
> i've been mainly using mysql lately so my oracle knowledge
> has totally dried up.
>
> how do i select a specific date from a date column?
>
> select * from table where date='05-AUG-02'
>
> returns nothing even though there are rows with that
> date. the only way i can get it to work is to do this
> horrible construction:
>
> select * from table where
> date BETWEEN TO_DATE('08/05/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
> AND TO_DATE('08/06/2002 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
>
>
> there's got to be a simpler way...
>
> -rs-
Received on Thu Aug 08 2002 - 23:09:54 CDT
