On 5/23/2013 4:55 PM, Daevid Vincent wrote:
I just noticed what I consider to be a bug; and related, has this been fixed
in later versions of MySQL?
We are using:
mysql Ver 14.12 Distrib 5.0.92, for portbld-freebsd8.1 (amd64) using 5.2
If you use BETWEEN and the same date for both parts (i.e. you want a single
day) it appears that the operator isn't smart enough to consider the full
day in the cases where the column is a DATETIME
http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_be
tween
WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16'
I actually have to format it like this to get results
WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND '2013-04-16
11:59:59'
From the Fine Manual...
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-conversion.html
###############
Conversion of DATE values:
Conversion to a DATETIME or TIMESTAMP value adds a time part of
'00:00:00' because the DATE value contains no time information.
...
Prior to MySQL 5.0.42, when DATE values are compared with DATETIME
values, the time portion of the DATETIME value is ignored, or the
comparison could be performed as a string compare. Starting from MySQL
5.0.42, a DATE value is coerced to the DATETIME type by adding the time
portion as '00:00:00'. To mimic the old behavior, use the CAST()
function to cause the comparison operands to be treated as previously.
For example:
date_col = CAST(datetime_col AS DATE)
###############
That seems pretty clear to me as not a bug.
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
