Convert dates stored as text to DATETIME-CodePudding

So, I import a CSV file into MySQL Workbench and the datetime columns are changed to text. I can't perform any calculations.

How do I convert the entire column from text to datetime? For example the date is in this format 2022-01-13 11:59:47, how do I change this to datetime?

CodePudding user response：

Here's a demo:

mysql> create table mytable ( t text );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mytable set t = '2022-01-13 11:59:47';
Query OK, 1 row affected (0.00 sec)

mysql> select * from mytable;
 --------------------- 
| t                   |
 --------------------- 
| 2022-01-13 11:59:47 |
 --------------------- 
1 row in set (0.00 sec)

mysql> alter table mytable modify column t datetime;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `t` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

mysql> select * from mytable;
 --------------------- 
| t                   |
 --------------------- 
| 2022-01-13 11:59:47 |
 --------------------- 
1 row in set (0.01 sec)

If you have any rows in your text column that aren't valid datetime values, they won't be converted successfully. In those cases, they'll turn into NULL. So be careful and create a backup before you do this alter table.

Better yet, do this operation on a copy of your data in another table, until you are confident it will work as you expect. Never try a potentially destructive change on the only copy of your data.

CodePudding user response：

Run the query on your data:

select cast(textdate as datetime)
from t;

show warnings;

If there are no warnings in the attempted datetime conversion, proceed with alter table:

alter table mytable modify column textdate datetime;