MySQL Using a string column with date text as a date field

Question

I am doing a database migration and I currently have a string column with dates (versus having the optimal datetime field set for these). Is there a function I can call in MySQL to convert this field to a datetime so I can use the date features such as before this date or after this date?

Answer 1

SELECT  *
FROM    mytable
WHERE   CAST(mydatefield AS DATETIME) >= CAST('2009-01-01' AS DATETIME)

If your dates are in some weird format that MySQL does not understand, use STR_TO_DATE:

SELECT  *
FROM    mytable
WHERE   STR_TO_DATE(mydatefield, '%Y, %d %m') >= STR_TO_DATE('2009, 01 01', '%Y, %d %m')

Answer 2

STR_TO_DATE function - ref

Example:

select str_to_date('10-oct-2006', "%d-%b-%Y");