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

STR_TO_DATE function - ref

Example:

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

Answer 2

You could always convert the date to a timestamp when retrieving it from the table:

SELECT *
FROM my_table
WHERE UNIX_TIMESTAMP(my_time) > UNIX_TIMESTAMP("2000-10-18 12:30:40");

If called with no argument, returns a Unix timestamp (seconds since '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since '1970-01-01 00:00:00' UTC. date may be a DATE string, a DATETIME string, a TIMESTAMP, or a number in the format YYMMDD or YYYYMMDD. The server interprets date as a value in the current time zone and converts it to an internal value in UTC. Clients can set their time zone as described in Section 9.7, “MySQL Server Time Zone Support”.

For more information, check out the documentation for UNIX_TIMESTAMP at the MySQL website.

Answer 3

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')