I am working on a site where dates have been stored as text strings in a mySQL database, such as '2009-10-17' , and I need to create a 'search by date' function where a given start and end date can be compared to the date stored in the database. In other words, i need to convert the text-formatted date to a real 'datetime' value , that can be compared with (greater than) and (less than) logic within the query itself.

Like all things CF/mySQL , there are usually multiple ways to get things done. After kicking it around a few different ways, I settled on mySQL's built-in "str_to_date" function as the easiest and most efficient solution.

The str_to_date()  function takes two arguments: the string that is to be converted, and a format string, which uses the same syntax as mysql date_format() and other related mySQL date functions.

Since the dates are entered in 'yyyy-mm-dd' format, my format string is" %Y-%m-%d "
(Note the capital Y which signifies a 4-digit year)

My query now looks something like this - note the second line:

str_to_date(aa.date_published,'%Y-%m-%d') as articleDate,
aa.title, aa.ID,
FROM articles_table ...

This converts the date, 2009-10-17,
to a datetime stamp,
{ts '2009-09-17 00:00:00'},
which can now be used for any type of date comparisons.

To keep things clean, if an article happens to be entered with a different format that does not match our format pattern (i.e. somebody types in '10/17/09'),  str_to_date() will simply return an empty string in that column, rather than throwing an error.

The mySQL specification for this and other functions is here: