List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:May 23 2013 8:55pm
Subject:Bug in BETWEEN same DATETIME
View as plain text  
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'
 
As it appears that in the first instance it defaults the time to 00:00:00
always, as verified by this:
 
WHERE `transaction_date` BETWEEN '2013-04-16' AND '2013-04-16 11:59:59'
 
So,  I think it's probably safe to assume that if someone is using the
BETWEEN on datetime columns, their intent more often than not is to get the
full 24 hour period, not the 0 seconds it currently pulls by default.
 
I also tried these hacks as per the web page above, but this doesn't yield
results either
 
WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATE) AND
CAST('2013-04-16' AS DATE)
WHERE `transaction_date` BETWEEN CAST('2013-04-16' AS DATETIME) AND
CAST('2013-04-16' AS DATETIME)
 
This one works, but I fail to see how it's any more beneficial than using a
string without the CAST() overhead?
 
WHERE `transaction_date` BETWEEN CAST('2013-04-16 00:00:00' AS DATETIME) AND
CAST('2013-04-16 11:59:59' AS DATETIME)
 
Or is there some other magical incantation that is supposed to be used
(without me manually appending the time portion)?

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