mysqltimestampsqldatatypesalter

MySQL convert data type from string to date

enter image description here

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!

Solution

  • 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)