Nicolas Nicolas - 6 months ago 14
MySQL Question

MySQL Check if Varchar Date is Between String Dates

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?

Answer

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.