Can MYSQL filter by date if date is stored as text? ex "02/10/1984"

Question

Hello!

I am trying to modify an app for a client which has already a database of over 1000 items. The dates are stored as text in the database with the format "02/10/1984". The system allows you to add and remove fields to the catalog dynamically and it also allows the advanced search to have specific fields be allowed.

The problem is that it wasn't designed with dates in mind, so when I set a field as a date, and try to search by a range the query is trying to do a AND (cfv0.value >= 01/02/2004 AND cfv0.value <= 05/03/2008) . I can make it so the date range passed is a numeric time value. Is there a way that when sending the query, it takes the text fields (with the date) and converts it to numeric time value so at that point I am basically just comparing numbers which would work fine.

I do not have the option to change all the current date to numeric value due to the way the dynamic fields are set up.

Thanks guys!

Answer 1

You'll want to convert both the query coming in and the date in the column, so something like this (assuming it's in month/day/year order):

AND STR_TO_DATE(cfv0.value, '%m/%d/%Y') >= STR_TO_DATE('01/02/2004', '%m/%d/%Y')

Answer 2

Note that in the future, if you'd like dates that are stored as a string to sort the same both alphabetically and chronologically, then store them in zero-padded, four digit year, month, day format, like this:

2004/01/20
2004/10/05
2006/12/31