[SOLVED] I have a column in which i have varchar data and i want to convert that data into TIMESTAMP

Issue

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 would like 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 do not want to alter the data manually. I would like to update my data with SQL query.

Solution

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');

Answered By – Tim Biegeleisen

Answer Checked By – Pedro (BugsFixing Volunteer)