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)
WHERE `transaction_date` = (new column stored as date)
On Thu, May 23, 2013 at 9:55 PM, Daevid Vincent <daevid@stripped> wrote:
> I just noticed what I consider to be a bug; and related, has this been
> 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
> 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
> 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)
> 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)?