Re: I have a problem with the oracle date comparison --plz help

jambesh_at_gmail.com wrote:
> Hi , every body
>
> I have written a query
>
> -------------------- QUERY------------------------------
> select count(*) from newtable where order_tm > TO_DATE('19-SEP-05' ,
> 'dd-mon-yy') and order_tm < to_date('21-SEP-05','dd-mon-yy') and
> status='APPROVE'
> ------------------------------------------------------------
>
>
> --------------------RESULT--------------------------------
> this give me a result of 50
> ----------------------------------------------------------
>
>
> when i remove count(*) to see all field and put * over ther i can see
> ...this query select the order from 19th sep also which it should not
> as i have given a greater then symbol on that condition.
>
> can you please explain me why it is happening so..??
>
>
> is this query be substitute as approve_tm = '20-SEP-05' and
> status='APPROVE' what is wrong with this query ??? it doesn't give
> currect result.

It does give the correct result, however not the result you expected. Your date comparison says 'give me all records where the date/time field is greater than the 19th of September, 2005 at midnight and less than the 21st of September, 2005 at midnight.' Your results are correct. You might want to rewrite your query as:

 select count(*) from newtable where trunc(order_tm) = to_date('20-SEP-05', 'DD-MON-YY') and status='APPROVE'

as that is the only date you're interested in. And, no, your original query is not a substitute for the query I've posted, and not a substitute for the similar query you've written.

David Fitzjarrell Received on Tue Sep 20 2005 - 16:18:49 CDT