List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 28 2000 2:22am
Subject:Re: BETWEEN doesn't work with dates like "2000-1-3"
View as plain text  
>>>>> "Oliver" == Oliver Billmann <oliver@stripped> writes:

Oliver> Hi Monty,
Oliver> Usually one doesn't have to supply two digit months and days in
Oliver> dates if they are below 10 (at least the manual says so ;-). But
Oliver> if you do this with BETWEEN, it doesn't work... :-(

>> This is a known problem;  It's even documented in the MySQL manual.

Oliver> Sorry, but I haven't seen this in the manual. Could you give me a pointer
Oliver> where I can find it? The only thing I've seen was that one can't mix
Oliver> DATE and DATETIME values, but as I only used DATE in the example, this
Oliver> doesn't apply...

>From the manual:

---------
expr BETWEEN min AND max
If expr is greater than or equal to min and expr is
less than or equal to max,BETWEEN returns 1,
otherwise it returns 0.  This is equivalent to the expression
(min <= expr AND expr <= max) if all the arguments are of the
same type.  (You can't compare DATE with DATETIME
arguments with BETWEEN as you can do with simple compare
operations like =) The first argument expr) determines
how the comparison is performed.  If expr is a case-insensitive
string expression, a case-insensitive string comparison is done.  If
expr is a case-sensitive string expression, a case-sensitive
string comparison is done.  If expr is an integer expression, an
integer comparison is done.  Otherwise, a floating-point (real)
comparison is done.
------------

Note that this doesn't mention anything about special comparison for
dates;  The date/datetime/timestamp is simply converted to a string
and compared as a string;  This is the source of the problem!

Anyway, I have however fixed this problem in the upcoming MySQL 3.23.9
release...

Regards,
Monty
Thread
BETWEEN doesn't work with dates like "2000-1-3"Oliver Billmann18 Jan
  • BETWEEN doesn't work with dates like "2000-1-3"Michael Widenius26 Jan
    • Re: BETWEEN doesn't work with dates like "2000-1-3"Oliver Billmann26 Jan
      • Re: BETWEEN doesn't work with dates like "2000-1-3"Michael Widenius28 Jan
        • Re: BETWEEN doesn't work with dates like "2000-1-3"Oliver Billmann28 Jan
          • Re: BETWEEN doesn't work with dates like "2000-1-3"Michael Widenius29 Jan