[thelist] Wierd MySQL Behavior
Jay Blanchard
jay.blanchard at niicommunications.com
Mon Apr 4 10:23:03 CDT 2005
[snip]
That's probably it. Additionally, the data types of startdate and endate
are
DATETIME. Should they be TIMESTAMP or TIME?
[/snip]
Well, this introduces another potential problem for your query.
http://www.mysql.com/date states ...
[quote]
DATE
A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL
displays DATE values in 'YYYY-MM-DD' format, but allows you to assign
values to DATE columns using either strings or numbers.
DATETIME
A date and time combination. The supported range is '1000-01-01
00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in
'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to
DATETIME columns using either strings or numbers.
[/quote]
You displayed your dates without dashes, and that could have a
non-positive impact on the query returning the proper data. Your query
will have to reflect the type used;
DATE -> WHERE myDate BETWEEN '2005-04-01' AND '2005-04-02' (BETWEEN is
better than <= or >= )
DATETIME -> WHERE myDate BETWEEN '2005-04-01 00:00:00' AND '2005-04-02
23:59:59'
ALSO -> WHERE SUBSTRING(myDate, 1, 10) BETWEEN '2005-04-01' AND
'2005-04-02' (syntax may be off slightly here)
More information about the thelist
mailing list