List:General Discussion« Previous MessageNext Message »
From:shawn green Date:May 23 2013 10:50pm
Subject:Re: Bug in BETWEEN same DATETIME
View as plain text  

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
Thread
Bug in BETWEEN same DATETIMEDaevid Vincent23 May
  • RE: Bug in BETWEEN same DATETIMETimothy R Peterson23 May
    • Re: Bug in BETWEEN same DATETIMEMichael Dykman23 May
      • RE: Bug in BETWEEN same DATETIMERick James23 May
  • Re: Bug in BETWEEN same DATETIMEAndrew Moore23 May
    • Re: Bug in BETWEEN same DATETIMEAndrew Moore23 May
  • Re: Bug in BETWEEN same DATETIMEshawn green23 May
    • RE: Bug in BETWEEN same DATETIMERick James23 May
      • Re: Bug in BETWEEN same DATETIMEshawn green24 May
        • RE: Bug in BETWEEN same DATETIMERick James24 May
          • Re: Bug in BETWEEN same DATETIMEshawn green24 May
        • Re: Bug in BETWEEN same DATETIMEhsv24 May
          • RE: Bug in BETWEEN same DATETIMERick James29 May