MySQL检查Varchar日期是否在字符串日期之间 [英] MySQL Check if Varchar Date is Between String Dates

问题描述

我有一个SQL数据库，其中有一个字段，该字段中保存的日期类似于01/31/2014，并且它是varchar类型的.即使我可以访问该数据库，到目前为止，更改结构也已超出了限制.

I have a SQL database where I have a field that holds a date like 01/31/2014 and it is of varchar type. Even though I have access to this database changing the structure is off limits as of now.

有什么方法可以检查Javascript中的字符串日期是否在数据库中我的varchar类型日期之间?

Is there any way to check if a string date in Javascript is between my varchar type date in the database?

我得到的错误是，当我选择搜索介于(例如08/31/2013和12/31/2013之间)的日期时，它会产生正确的查询结果，但是当我搜索介于(例如08/31/2013和)即使有，我也没有从我的任务中找到任何结果.

The error I am getting is that when I choose to search for dates between (e.g. 08/31/2013 and 12/31/2013) it yields the correct query results, however when I search for dates between (e.g. 08/31/2013 and 01/01/2014) I get no results found from my quest even though there is.

我认为此错误与date列的结构不是DATE有关.这是真的，我该如何克服我的问题?

I believe this error is something to do with the date column's structure not being a DATE. Is this true and how can I overcome my problem?

推荐答案

您正在寻找的SQL相信是:

The SQL you are looking for I believe is:

WHERE STR_TO_DATE(column, '%m/%/d/%Y') 
BETWEEN STR_TO_DATE(column, '%m/%/d/%Y')
AND STR_TO_DATE(column, '%m/%/d/%Y')

MySQL具有STR_TO_DATE函数，您可以使用该函数将字符串解析为MySQL中的日期对象.然后，您可以使用之间的关键字进行比较.

MySQL has a STR_TO_DATE function, which you can use to parse a string into a date object in MySQL. Then you can use the between keyword for comparison.

我建议您以毫秒格式存储日期.这将减少混乱和麻烦，并且它也是许多编码语言的标准.这也将使比较和转换日期变得更加容易.

My suggestion to you is to store your dates in millisecond format. It will create a lot less confusion and hassle, and it is also a standard across many coding languages. It will also make comparing and converting dates significantly easier.

这篇关于MySQL检查Varchar日期是否在字符串日期之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！