>>>>> "Peter" == Peter Campbell <pc@stripped> writes:
Peter> I have a similar date problem with queries;
Peter> The following query works as expected, getting all November
Peter> SELECT * FROM TRAN WHERE DATE >= '1999-09-01' AND DATE <=
Peter> SELECT * FROM TRAN WHERE DATE BETWEEN '1999-09-01' AND
Peter> ignores all transactions on the last day of the month, changing the
Peter> date to an invalid '1999-09-31' works fine (or appending the time
Peter> 23:59 to the datetime field as I have changed my code to do).
Peter> Note: all my date fields have a 00:00:00 time value.
Peter> Mysql version = 3.23.5-alpha
The problem is that 'BETWEEN' doesn't automaticly convert a DATE to a
DATETIME string. (simple compares (<=, <, >=, > and = does this as
without this conversion many ODBC applications would crash).
In effect you are comparing the strings
'1999-09-30 00:00:00' <= '1999-09-30', which isn't true.
I had now added some more information into the MySQL documentation that
explains this better.