Re: date column select question

Ralph Snart wrote:

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

You are making one common mistake, one classic.

Try this:

SELECT *
FROM table
WHERE TRUNC(date_field) = TO_DATE('08-AUG-2002');

Always use TO_DATE
Always use TRUNC to set the hours, minutes, and seconds to 00:00:00 And TO_DATE don't write screwy dates in TO_DATE and then reformat them. Just use the valid format in the first place.

Daniel Morgan Received on Sun Aug 11 2002 - 09:08:48 CDT