Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle SQL query by date
"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. >| >|
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