>> where cast(transaction_date as date) BETWEEN '2013-04-16' AND
This approach might be problematic in that it requires that every row in
the source table be examined so that it's transaction_date can be casted.
The original formulation is more efficient as it allows an index on
transaction_date to be used, if one exists.
>> WHERE `transaction_date` BETWEEN '2013-04-16 00:00:00' AND
'2013-04-16 23:59:59'
Although you probably get the result you want by just incrementing the day
on the upper-limit.
WHERE `transaction_date` BETWEEN '2013-04-16 AND '2013-04-17'
- michael dykman
On Thu, May 23, 2013 at 5:07 PM, Peterson, Timothy R <
timothy_r_peter...@uhc.com> wrote:
> 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
>
>
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
