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
-----Original Message-----
From: Daevid Vincent [mailto:dae...@daevid.com]
Sent: Thursday, May 23, 2013 3:56 PM
To: mysql@lists.mysql.com
Subject: Bug in BETWEEN same DATETIME
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#operato
r_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)?
This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
