XPost: comp.databases.oracle.server   
   From: kennedy-downwithspammersfamily@attbi.net   
      
   "vnl"  wrote in message   
   news:Xns9535C4FA974ABvnl999@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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)