XPost: comp.databases.oracle.server   
   From: mcstockX@Xenquery   
      
   "vnl"  wrote in message   
   news:Xns9536D3916232Avnl999@216.196.97.131...   
   | "Mark C. Stock"  wrote in   
   | news:KKOdnRkaRKCxIJfcRVn-gQ@comcast.com:   
   |   
   | >   
   | > "vnl"  wrote in message   
   | > news:Xns9536A0A6881EEvnl999@216.196.97.131...   
   | >| afilonov@yahoo.com (Alex Filonov) wrote in   
   | >| news:336da121.0407300808.690ded59@posting.google.com:   
   | >|   
   | >| > vnl  wrote in message   
   | >| > news:...   
   | >| >> 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.   
   | >|   
   | >| Thanks everyone. The field did turn out to be a "date" type field.   
   | >|   
   | >| I was eventually able to get it to work by using the following   
   | >| format:   
   | >|   
   | >| SELECT field1, field2, date_and_time,   
   | >| FROM table1   
   | >| WHERE TRUNC(date_and_time)=TO_DATE('31-dec-2002','dd-MON-yyyy')   
   | >|   
   | >| What was weird was that I was getting different results in the   
   | >| date_and_time field depending on whether I was running the SQL in   
   | >| Toad, Oracle's SQL program (forgot name), and Crystal Reports SQL   
   | >| Designer. Two showed both the date and time, the other showed just   
   | >| the date while running the same SQL query. It got even worse as I   
   | >| tried to import the data into Excel and Access which added further   
   | >| formatting decisions.   
   | >|   
   | >| I'm still working on getting the SQL query to remove the time   
   | >| entirely so that I will just have the date in that field.   
   | >|   
   | >| Thanks.   
   | >|   
   | >|   
   | >   
   | > you're not really getting different results, the different tools are   
   | > displaying the results differently   
   | >   
   | > oracle date columns are stared in an internal 7 byte binary format   
   | > which is not directly displayable, but always must be converted to a   
   | > character format by any tool that is attempting to display dates --   
   | > some tools, like TOAD, choose their on date/time format for converting   
   | > the data, others, like SQL*Plus pick up the default format for the   
   | > session, which is usually DD-MON-RR   
   | >   
   | > regarding working on removing the time entirely -- that's the better   
   | > use of the TRUNC function, in your select list. if you get in the   
   | > habit of using TRUNC in the WHERE clause, you may well end up writing   
   | > poor some very poorly performing code once you start working with   
   | > production tables, since using an expression on a column in the WHERE   
   | > clause will prevent Oracle from using any available index on that   
   | > column, unless the index is a function-based index (there are other   
   | > considerations as to whether or not oracle will user an index, but   
   | > this is a typical performance error)   
   | >   
   | > try rewriting the query so you don't have use TRUNC in the where   
   | > clause -- this usually involves using a BETWEEN expression or a >= & <   
   | > pair of expressions; or, make sure you understand function based   
   | > indexes   
   | >   
   | > ++ mcs   
   | >   
   |   
   | Would this be the correct format?:   
   |   
   | SELECT field1, field2, TRUNC(date_and_time),   
   | FROM table1   
   | WHERE date_and_time=TO_DATE('31-dec-2002','dd-MON-yyyy')   
   |   
   |   
   | Thanks.   
      
   in the select list, yes   
   but your where clause will only find rows for 12/31/02 that have no time   
   element stored in the date_and_time column   
   look at the between operator or look into using a '>=' along with a '<'   
   operator   
      
   ++ mcs   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)