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.