I have a column in which i have varchar data and i want to convert that data into TIMESTAMP-CodePudding

SO my data which I have in varchar (datatype) is in this form

14-12-2021 Tuesday 14:30:00
22-11-2021 Monday 13:00:00
05-10-2021 Tuesday 11:45:00
24-09-2021 Friday 10:15:00
11-08-2021 Wednesday 12:15:00
01-07-2021 Thursday 00:45:00
17-06-2021 Thursday 03:30:00
21-05-2021 Friday 02:15:00
04-04-2021 Sunday 02:45:00

And i want this data to convert in TIMESTAMP

My Output Should be:

14-12-2021  14:30:00
22-11-2021  13:00:00
05-10-2021  11:45:00
24-09-2021  10:15:00
11-08-2021  12:15:00
01-07-2021  00:45:00
17-06-2021  03:30:00
21-05-2021  02:15:00
04-04-2021  02:45:00

I have tried many things but nothing is helping me out can someone please help me i dont want to ALTER data manually i want to update my data with sql query.

CodePudding user response：

Assuming you want to convert your data to a bona fide timestamp, you may use STR_TO_DATE:

SELECT STR_TO_DATE(CONCAT(SUBSTRING_INDEX(col, ' ', 1), ' ',
                          SUBSTRING_INDEX(col, ' ', -1)),
                   '%d-%m-%Y %H:%i:%s') AS ts_out
FROM yourTable;

Assuming you just want to view your data this way, you may use:

SELECT CONCAT(SUBSTRING_INDEX(col, ' ', 1), ' ',
              SUBSTRING_INDEX(col, ' ', -1)) AS ts_out
FROM yourTable;

To update your table, first create a new timestamp column ts_out then populate it using:

UPDATE yourTable
SET ts_out = STR_TO_DATE(CONCAT(SUBSTRING_INDEX(col, ' ', 1), ' ',
                                SUBSTRING_INDEX(col, ' ', -1)),
                         '%d-%m-%Y %H:%i:%s');