Re: Oracle SQL query by date
Date: Fri, 30 Jul 2004 17:25:32 -0400
Message-ID: <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.
|
|