mysql - 如何在两个日期之间查询 'date' 字段是一个字符串？

Question

我在 MySQL 数据库中有一个表，其中有一个名为“日期”的字段，问题是日期的格式是 DD-MM-YYYY，所以我无法将它作为 DATE 类型字段上传到 MySQL。相反，字段类型是一个字符串。考虑到这一点，我该如何编写会产生这种效果的查询 -

SELECT * FROM `table` 
WHERE  (date_field BETWEEN '2010-01-30' AND '2010-09-29')

记住“date_field”不是 DATE 类型，而是字符串。

Answer 1

SELECT * FROM `table` 
WHERE str_to_date(date_field, '%d-%m-%Y') BETWEEN '2010-01-30' AND '2010-09-29'

SQLFiddle example

编辑

你也可以这样做:

SELECT * FROM `table` 
WHERE cast(substring(date_field, 7, 4) as unsigned) * 10000 + cast(substring(date_field, 4, 2) as unsigned) * 100 + cast(substring(date_field', 1, 2) as unsigned)
BETWEEN 20100130 AND 20100929