mysql查询varchar的时间_MySQL检查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.

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

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.

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?

解决方案

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 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.