Re: Oracle SQL query by date

From: vnl <vnl999_at_vnl999.invalid>
Date: Mon, 02 Aug 2004 18:02:33 -0500
Message-ID: <Xns9539C1AE02E3vnl999_at_216.196.97.131>

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in news:ObCdndEMafKn6JbcRVn-hw_at_comcast.com:

>
> "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
>

This is the code that I was eventually able to get working. Any suggestions about making it better?

SELECT field1, field2, TO_CHAR(date_and_time, 'DD-MON-YYYY') AS date_entry
FROM table1
WHERE TRUNC(date_and_time)=TO_DATE('01-sep-2002', 'DD-MON-YYYY')

I tried using trunc in the select clause and was getting weird results ... The year was coming out as "0003" instead of "2003"

Thanks. Received on Tue Aug 03 2004 - 01:02:33 CEST