如何在两个日期之间使用“日期"字段查询字符串? [英] How can I query between two dates with the 'date' field is a string?

问题描述

我在MySQL数据库中有一个表，该表的字段名为'Date'，问题是日期的格式为DD-MM-YYYY，所以我不能将其作为DATE类型的字段上传到MySQL.而是字段类型是字符串.考虑到这一点，我该如何编写将产生这种效果的查询-

I have a table in a MySQL database with a field named 'Date', the problem is the date is in the format DD-MM-YYYY so I cannot upload it to MySQL as a DATE type field. Instead the field type is a string. With this in mind, how can I write query that will give this effect -

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

请记住，"date_field"不是DATE类型，而是字符串.

keeping in mind 'date_field' is not of DATE type, but string.

推荐答案

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

SQLFiddle示例

您也可以执行以下操作:

You can also do this:

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

这篇关于如何在两个日期之间使用“日期"字段查询字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！