mysql：搜索存储为 varchar 的 BETWEEN 日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3471094/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql: searching BETWEEN dates stored as varchar
提问by l--''''''---------''''''''''''
i would like to select * from table where dates between (some_date and another_date)
我想要 select * from table where dates between (some_date and another_date)
the problem is that the dates are stored as varchar!
问题是日期存储为varchar!
here are examples of dates that i have:
以下是我拥有的日期示例：
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
我根本不在乎时间 我只需要日期
reporttimeis the datefield
reporttime是date字段
this is not working:
这不起作用：
SELECT * FROM batchinfo
where cast(reporttime as date) between ('7/28/10' and '7/29/10')
this:
这个：
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')
is returning:
正在返回：
Truncated incorrect datetime value: '7/8/2010 11:47 AM'
Incorrect datetime value: '0.00012009' for function str_to_date
this:
这个：
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')
is returning:
正在返回：
Incorrect datetime value: '7/8/2010 11:47 AM' for function str_to_date
OMG PONIES:
天啊小马：
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
采纳答案by Mchl
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()).
您想在日期之间搜索，将它们存储为日期。通过将它们存储为字符串，您就是在用脚射击自己。您基本上需要从字符串中提取日期部分（使用SUBSTR()或LEFT()）并将其解析为日期格式（使用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'
STR_TO_DATE(LEFT(reporttime,LOCATE(' ',reporttime)),'%m/%d/%Y') BETWEEN '2010-07-28' AND '2010-07-29'
回答by OMG Ponies
Use STR_TO_DATE to convert the strings to the DateTime data type. The format shorthand is found under DATE_FORMAT:
使用STR_TO_DATE 将字符串转换为 DateTime 数据类型。格式简写位于DATE_FORMAT下：
STR_TO_DATE(column, '%m/%/d/%Y %h:%i:%s %p')
The problem is, you'll have to update the VARCHAR dates to all be the same formatfirst, before you can use:
问题是，您必须先将VARCHAR 日期更新为相同的格式，然后才能使用：
WHERE STR_TO_DATE(reporttime, '%m/%/d/%Y %h:%i:%s %p') BETWEEN STR_TO_DATE(some_date, '%m/%/d/%Y')
AND STR_TO_DATE(another_date, '%m/%/d/%Y')
Date formats are not consistent (some use hyphens, others slashes and Year/Month/day order can be totally different...), so STR_TO_DATE is the most accommodating & consistent means of turning a string into a DateTime. Only after the value is DateTime, does Date/Time functionality become available like DATE() to get only the date portion...
日期格式不一致（有些使用连字符，有些使用斜线，而年/月/日顺序可能完全不同……），因此 STR_TO_DATE 是将字符串转换为 DateTime 的最方便和一致的方法。只有在值为 DateTime 之后，日期/时间功能才变得可用，例如 DATE() 以仅获取日期部分...
Because of the data type change, an index on some_date & another_date columns can not be used.
由于数据类型更改，无法使用 some_date 和 another_date 列上的索引。
回答by wizzardz
IF you are using SQl use this query
如果您使用的是 SQL，请使用此查询
Data
数据
DECLARE @Dates TABLE (StartDate varchar(100));
INSERT INTO @Dates VALUES ('7/1/2010 9:10 AM');
INSERT INTO @Dates VALUES ('7/5/2010 10:33 AM');
INSERT INTO @Dates VALUES ('7/13/2010 04:53 AM');
INSERT INTO @Dates VALUES ('7/22/2010 8:45 AM');
INSERT INTO @Dates VALUES ('7/10/2010 11:20 AM');
INSERT INTO @Dates VALUES ('7/11/2010 12:40 AM');
Query:
询问：
SELECT * FROM @Dates
WHERE (CONVERT(datetime,StartDate,101) >= CONVERT(datetime,'7/1/2010 9:10 AM',101))
AND (CONVERT(datetime,StartDate,101) <= CONVERT(datetime,'7/15/2010 9:10 AM',101))
ORDER BY CONVERT(datetime,StartDate,101)
回答by Muhammad Fahad
This Date query will work perfectly...
此日期查询将完美运行...
Database 'Date' Will be like '01/01/2016' and datatype is 'varchar'
SELECT * FROM `test` WHERE STR_TO_DATE(`test`.`date`, '%d/%m/%Y') BETWEEN '2016-01-01' AND '2016-01-31' ORDER BY `test`.`date` ASC
回答by Bipan
If date is in Varchar
如果日期在 Varchar 中
Query AND c.sentdate > '2016-jun-15'
查询 AND c.sentdate > '2016-jun-15'
回答by bobs
You can CASTyour varchar values to DATETIME or DATE type.
您可以CAST将 varchar 值设置为 DATETIME 或 DATE 类型。
WHERE CAST(dates AS DATE) BETWEEN (7/28/10 and 7/29/10)
This might not be an optimal solution, because you may lose the benefit that indexes provide.
这可能不是最佳解决方案，因为您可能会失去索引提供的好处。
