Re: Oracle SQL query by date

From: vnl <vnl999_at_vnl999.invalid>
Date: Fri, 30 Jul 2004 19:48:20 -0500
Message-ID: <Xns9536D3916232Avnl999@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. Received on Fri Jul 30 2004 - 19:48:20 CDT