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

You are making the mistake of comparing a string to a date. Compare a date to a date. '01-SEP-02' is a string , date_and_time is a date. Do it like:  SELECT field 1, field2, date_and_time,
 FROM table1
 WHERE date_and_time >=to_date( '01-SEP-02','dd-mmm-yy') and date_and_time <= to_date('01-DEC-02','dd-mmm-yy');

You should really use 4 didgit years unless you really mean the year 2. So it should be:
 SELECT field 1, field2, date_and_time,
 FROM table1
 WHERE date_and_time >=to_date( '01-SEP-2002','dd-mmm-yyyy') and

    date_and_time <= to_date('01-DEC-2002','dd-mmm-yyyy');

Jim Received on Thu Jul 29 2004 - 21:59:50 CDT