Convert unconventional string to mysql datetime in MySQL

Question

I want to convert a field that represents a date-time but is currently a VARCHAR to a DATETIME field. Currently the representation looks like: 'Sun May 20 01:04:39 +0000 2012' I want to do this operation in a query.

Answer 1

Use STR_TO_DATE

SELECT STR_TO_DATE('Sun May 20 01:04:39 +0000 2012', '%a %M %d %H:%i:%S +0000 %Y')

• SQLFiddle Demo
• Date Format

Answer 2

Use the DATE function.

This does the job and can be used inside queries.