Re: Oracle SQL query by date

"vnl" <vnl999_at_vnl999.invalid> wrote in message news:Xns9536A0A6881EEvnl999_at_216.196.97.131...
| afilonov_at_yahoo.com (Alex Filonov) wrote in
| news:336da121.0407300808.690ded59_at_posting.google.com:
|
| > 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.
|
| 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 Received on Fri Jul 30 2004 - 16:25:32 CDT