Re: Oracle SQL query by date

vnl <vnl999_at_vnl999.invalid> wrote in message news:<Xns9535C4FA974ABvnl999_at_216.196.97.131>...
> I'm trying to run a SQL query but can't find any records when trying to
> select a certain date. Here's the sql:
>
> SELECT field 1, field2, date_and_time,
> FROM table1
> WHERE date_and_time = '01-SEP-02'
>
> I'm getting no results. The date_and_time field is formatted like this:
>
> 2002-SEP-02 00:01:04
>

And here lies the problem. You date is not equal to '01-SEP-02', it has time component as well. You can either include time in your query condition:

WHERE date_and_time = to_date('2002-SEP-02 00:01:04', 'YYYY-MON-DD HH24:MI:SS')

or use trunc function to truncate date before comparing it to the constant:

WHERE trunc(date_and_time) = '01-SEP-02'

> When I run a range, the results show that records do occur on the single
> date that I am looking for:
>
> SELECT field 1, field2, date_and_time,
> FROM table1
> WHERE date_and_time >= '01-SEP-02' and date_and_time <= '01-DEC-02'
>
> I'm wondering whether the problem may have something to do with the date
> field containing both the date and time. Any suggestions?
>
> Thanks.
Received on Fri Jul 30 2004 - 11:08:53 CDT