Bug in BETWEEN same DATETIME

Question

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_between

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)?

Answer 1

You probably want where cast(transaction_date as date) BETWEEN '2013-04-16' AND
'2013-04-16' That works on my test case
You could also change the where clause to be >= date and < date+1

Answer 2

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)