mysql:搜索存储为varchar的日期之间 [英] mysql: searching BETWEEN dates stored as varchar

问题描述

我想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

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

please notice that some records have seconds and some do not

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

i dont care about the time at all i just need the date

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小马:

我正在把第一个空格前的所有东西都拿走:

i am taking everything before the first blank:

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

现在我得到这个退货了:

and now i get this returned:

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

推荐答案

您要在日期之间进行搜索，并将其存储为日期.通过将它们存储为字符串，您就可以自己射击. 基本上，您需要从字符串中提取日期部分(使用SUBSTR()或LEFT())并将其解析为日期格式(使用STR_TO_DATE()).

You want to search between dates, store them as dates. By storing them as strings you're shooting yourself in the foot. You'd basically need to extract date part from the string (using SUBSTR() or LEFT() ) and parse it to date format (using STR_TO_DATE()).

这种解决方案的性能令人赞叹.

The performance of such solution will be appaling.

STR_TO_DATE(LEFT(reporttime,LOCATE(' ',reporttime)),'%m/%d/%Y') BETWEEN '2010-07-28' AND '2010-07-29'

这篇关于mysql:搜索存储为varchar的日期之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！