List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:December 27 1999 10:32pm
Subject:Re: MySQL Invalid Date Problem
View as plain text  
>>>>> "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> transactions.

Peter> SELECT * FROM TRAN WHERE DATE >= '1999-09-01' AND DATE <= 
Peter> '1999-09-30'

Peter> However

Peter> SELECT * FROM TRAN WHERE DATE BETWEEN '1999-09-01' AND 
Peter> '1999-09-30'

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


Hi!

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.

Regards,
Monty
Thread
MySQL Invalid Date ProblemEnoch Chan13 Dec
  • Re: MySQL Invalid Date Problemsinisa13 Dec
    • Re: MySQL Invalid Date ProblemPeter Campbell13 Dec
      • Re: MySQL Invalid Date ProblemMichael Widenius28 Dec
RE: MySQL Invalid Date ProblemQuentin Bennett14 Dec