Personally I don't share your view that it's a bug. Omitting the time results in midnight by default so this screws between because there's no time between 00:00:00 and 00:00:00.
Are you having operational issues here or are you simply fishing for bugs? WHERE `transaction_date` = DATE(datetime) or WHERE `transaction_date` = (new column stored as date) On Thu, May 23, 2013 at 9:55 PM, Daevid Vincent <dae...@daevid.com> 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' > > 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)? >
