XPost: comp.databases.oracle.server
From: mcstockX@Xenquery
"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
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
