Mysql - How to convert date from ISO 8601 format and save to mysql column?

Question

I've a column in a table (varchar) with dates in this format 2013-09-05T10:10:02Z

How do I convert this into datetime format and save it in another column, using an update query?

Answer 1

You can use the STR_TO_DATE function:

UPDATE table1 SET col2 = STR_TO_DATE(col1,'%Y-%m-%dT%TZ')

Example:

mysql> select STR_TO_DATE('2013-09-05T10:10:02Z','%Y-%m-%dT%TZ');
+----------------------------------------------------+
| STR_TO_DATE('2013-09-05T10:10:02Z','%Y-%m-%dT%TZ') |
+----------------------------------------------------+
| 2013-09-05 10:10:02                                |
+----------------------------------------------------+
1 row in set (0.00 sec)