MySQL convert data type from string to date-CodePudding

Hi all,

I have 2 column in my table (Start Date & End Date), which are actually text. I want to convert them to timestamp format but I'm not sure how to do it. The query below is what I have tried but doesn't work:

 ALTER TABLE mytable 
MODIFY COLUMN STR_TO_DATE(`Start Date`,"%m/%d/%Y %H:%i") TIMESTAMP, 
MODIFY COLUMN STR_TO_DATE(`End Date`,"%m/%d/%Y %H:%i") TIMESTAMP;

May I know how should I alter the data type for these 2 columns in my table? Any help or advise will be greatly appreciated!

CodePudding user response：

Let's say this is our initial column definition state:

mysql> SHOW CREATE TABLE mytable;
 --------- ------------------------------------------------------------------------------------------------------------------------------------ 
| Table   | Create Table                                                                                                                       |
 --------- ------------------------------------------------------------------------------------------------------------------------------------ 
| mytable | CREATE TABLE `mytable` (
  `Start_Date` text,
  `End_Date` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
 --------- ------------------------------------------------------------------------------------------------------------------------------------ 
1 row in set (0.00 sec)

First you need to use update :

UPDATE mytable SET End_Date = STR_TO_DATE(End_Date,'%m/%d/%Y %H:%i');
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM mytable;
 --------------------- --------------------- 
| Start_Date          | End_Date            |
 --------------------- --------------------- 
| 2022-10-16 10:35:00 | 2022-10-16 10:40:00 |
| 2022-10-16 09:18:00 | 2022-10-16 09:25:00 |
 --------------------- --------------------- 
2 rows in set (0.00 sec)

Then Alter table to change column data type to 'timestamp':

mysql> ALTER TABLE mytable MODIFY COLUMN Start_Date TIMESTAMP, MODIFY COLUMN End_Date TIMESTAMP;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

If we test it,

mysql> SHOW CREATE TABLE mytable;
 --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| Table   | Create Table                                                                                                                                                                     |
 --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
| mytable | CREATE TABLE `mytable` (
  `Start_Date` timestamp NULL DEFAULT NULL,
  `End_Date` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
 --------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1 row in set (0.00 sec)