"Mark C. Stock" <mcstockX@Xenquery .com> wrote in
news:ObCdndEMafKn6JbcRVn-hw@comcast.com:
[color=blue]
>
> "vnl" <vnl999@vnl999.invalid> wrote in message
> news:Xns9536D3916232Avnl999@216.196.97.131...
>| "Mark C. Stock" <mcstockX@Xenquery .com> wrote in
>| news:KKOdnRkaRKCxIJfcRVn-gQ@comcast.com:
>|
>| >
>| > "vnl" <vnl999@vnl999.invalid> wrote in message
>| > news:Xns9536A0A6881EEvnl999@216.196.97.131...
>| >|
afilonov@yahoo.com (Alex Filonov) wrote in
>| >| news:336da121.0407300808.690ded59@posting.google.c om:
>| >|
>| >| > vnl <vnl999@vnl999.invalid> wrote in message
>| >| > news:<Xns9535C4FA974ABvnl999@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[/color]
like[color=blue]
>| >| >> this:
>| >| >>
>| >| >> 2002-SEP-02 00:01:04
>| >| >>
>| >| >
>| >| > And here lies the problem. You date is not equal to '01-SEP-02',[/color]
it[color=blue]
>| >| > 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-[/color]
DD[color=blue]
>| >| > 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[/color]
the[color=blue]
>| >| >> 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[/color]
suggestions?[color=blue]
>| >| >>
>| >| >> 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[/color]
converting[color=blue]
>| > 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[/color]
writing[color=blue]
>| > poor some very poorly performing code once you start working with
>| > production tables, since using an expression on a column in the[/color]
WHERE[color=blue]
>| > 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 >= &[/color]
<[color=blue]
>| > 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[/color]
time[color=blue]
> element stored in the date_and_time column
> look at the between operator or look into using a '>=' along with a '<'
> operator
>
> ++ mcs
>[/color]
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.
