Re: Oracle SQL query by date

"vnl" <vnl999_at_vnl999.invalid> wrote in message news:Xns9536D3916232Avnl999_at_216.196.97.131...
| "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in
| news:KKOdnRkaRKCxIJfcRVn-gQ_at_comcast.com:
|
| >
| > "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
| >
|
| 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 Received on Sat Jul 31 2004 - 12:30:51 CEST