mysql alter date,无法在MySql中将varchar转换为datetime

In my MySql table there's a column called _time of type varchar. The values it holds are in the format: year month day hour minute without the whitespaces: 201409201945 I want to convert it to datetime so I'm doing this:

ALTER TABLE `my_table` CHANGE COLUMN `_time` `date_time` DATETIME NOT NULL;

And it throws this error for some reason:

Error Code: 1292. Incorrect datetime value: '201409201945' for column '_date_time' at row 1 0.036 sec

解决方案

The three steps @Arkain mentioned would be with the help of the function STR_TO_DATE

-- add the new column

ALTER TABLE `my_table` ADD COLUMN `date_time` DATETIME;

-- update the new column with the help of the function STR_TO_DATE

UPDATE `my_table` SET `date_time` = STR_TO_DATE(`_time`, '%Y%m%d%H%i');

-- drop the old column

ALTER TABLE `my_table` DROP COLUMN `_time`;

The complete list of specifiers for STR_TO_DATE can be found at DATE_FORMAT, here an excerpt with those I used:

%d Day of the month, numeric (00..31)

%H Hour (00..23)

%i Minutes, numeric (00..59)

%m Month, numeric (00..12)

%Y Year, numeric, four digits

If the new column should have the attribute NOT NOLL, one way could be to set the sql mode before the operation to '' and reset the sql_mode later on:

SET @old_mode = @@sql_mode;

SET @@sql_mode = ''; -- permits zero values in DATETIME columns

ALTER TABLE `my_table` ADD COLUMN `date_time` DATETIME NOT NULL;

UPDATE `my_table` SET `date_time` = STR_TO_DATE(`_time`, '%Y%m%d%H%i');

ALTER TABLE `my_table` DROP COLUMN `_time`;

SET @@sql_mode = @old_mode;