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

jambesh_at_gmail.com schrieb:
> 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.
>

Because your format mask in to_date() is without time part, your query returns all rows from the 19th september which have time part greater than 0 hours 0 minutes 0 seconds as well. ( to_date('19-SEP-05','dd-mon-yy') is the same as to_date('19-SEP-05 00:00:00 dd-mon-yy hh24:mi:ss') )

If you want completely exclude records from 19th september, you could write instead

... where order_tm >=to_date('20-SEP-05','dd-mon-yy')...

Best regards

Maxim Received on Tue Sep 20 2005 - 16:16:11 CDT