Search code examples
mysqltimestampvarchar

mysql how to convert varchar(10) to TIMESTAMP?

I have stored all the date into my database as varchar(10), now I want to converse them into TIMESTAMP.

When I run sql

ALTER TABLE  `demo3` CHANGE  `date`  `date` TIMESTAMP NOT NULL

it alert:

#1292 - Incorrect datetime value: '1320534000' for column 'date' at row 1

BTW, All my date formart are 10 digital number.

Solution

  • You should first change the timestamp to datetime and then can change the type of column.

    ALTER TABLE `demo3` MODIFY COLUMN `date` varchar(25);

UPDATE `demo3` SET `date`= FROM_UNIXTIME(`date`);

ALTER TABLE  `demo3` CHANGE  `date`  `date` TIMESTAMP NOT NULL