我想要 select * from table where dates between (some_date and another_date)

问题是日期存储为varchar！

这是我有约会的例子：

7/29/2010 9:53 AM
7/16/2010 7:57:39 AM

请注意，有些记录有几秒钟，有些则没有

我根本不在乎时间，我只需要日期

reporttime是date领域

这是行不通的：

SELECT * FROM batchinfo 
 where cast(reporttime as date) between ('7/28/10' and '7/29/10')

这：

SELECT * from batchinfo WHERE reporttime BETWEEN STR_TO_DATE(7/28/2010, '%m/%/d/%Y %h:%i:%s %p')
                AND STR_TO_DATE(7/29/2010, '%m/%/d/%Y %h:%i:%s %p')

正在返回：

Truncated incorrect datetime value: '7/8/2010 11:47 AM'
Incorrect datetime value: '0.00012009' for function str_to_date

这：

SELECT * from batchinfo WHERE STR_TO_DATE(reporttime, '%m/%/d/%Y %h:%i:%s %p') BETWEEN STR_TO_DATE(7/28/2010, '%m/%/d/%Y')
                                                           AND STR_TO_DATE(7/29/2010, '%m/%/d/%Y')

正在返回：

Incorrect datetime value: '7/8/2010 11:47 AM' for function str_to_date

OMG小马：

我把所有东西都放在第一个空格之前：

SELECT * from batchinfo WHERE STR_TO_DATE(LEFT(reporttime,LOCATE(' ',reporttime)), '%m/%/d/%Y') BETWEEN STR_TO_DATE(7/28/2010, '%m/%/d/%Y')
                                                           AND STR_TO_DATE(7/29/2010, '%m/%/d/%Y')

现在我得到这个返回：

Incorrect datetime value: '7/8/2010' for function str_to_date