Sorry, that was meant to be; WHERE (new column stored as date) = '2013-04-16'
On Thu, May 23, 2013 at 10:16 PM, Andrew Moore <eroomy...@gmail.com> wrote: > 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)? >> > >
