Re: Difference between SYSDATE and TO_DATE

"mcstock" <mcstockx_at_xenquery.com> wrote in news:otidnbgRr-rZQjyiRVn-uw_at_comcast.com:

> Wolfgang, you didn't read very carefully before you got on your
> soapbox 
> 
> Tine was illustrating how the BETWEEN operator works, not how multiple
> predicates are evaluated.
> 

I am not so sure that I totally misunderstood Tine. The and in the between clause has absolutely nothing to do with the logical AND he (or she?) is explaining below. The and in between expresses a range, not a 1st and 2nd argument and if the lower bound is greater than the upper bound the range is empty and the between clause evaluates to false. You can't just take part of it and say it is false and I therefore do not need to look at the rest.

>> >
>> > There is AND logical operator between both arguments.
>> > As with most recent programming languages Oracle also
>> > evaluates truth of 1st argument and if not true it is
>> > logical, 2nd argument makes no sense:
>> >
>> > Arg1 AND Arg2 Outcome
>> > 1 1 TRUE, 2nd argument will be considered in evaluation
>> > 1 0 FALSE, 2nd argument will be considered in evaluation
>> > 0 0 FALSE, only 1st argument is evaluated
>> > 0 1 FALSE, only 1st argument is evaluated <- YOUR CASE!!
>> >
>> > BETWEEN can be interpreted like:
>> >
>> > operand > arg1 AND operand < arg2
>> >

Even that is false. between is replaced by the optimizer with

 " operand >= lower bound AND operand <= upper bound "

which makes it obvious why combined they are false when the lower bound is greater than the upper bound.

-- 
What lies behind us and what lies before us are small matters when 
compared to what lies within us.

Wolfgang Breitling
Oracle 7, 8, 8i, 9i OCP