Convert datetime in str to date and time

[David Kaeser]

Hi everyone,

I have a dataset where my date and time information is in a column in the following format "2014-12-10 23:00:00.000" and has a data type of TEXT. I would like to convert this column to a data column and a time column instead. How can I achieve this? So far I have tried to execute:

INSERT INTO all_dates_and_time(date_col, time_col)
SELECT CONVERT(DATE, SELECT STR_TO_DATE((select date_and_time_text FROM all_dates_and_time),'yyyy-mm-dd hh:mi:ss(24h)')),
SELECT CONVERT(TIME, SELECT STR_TO_DATE((select date_and_time_text FROM all_dates_and_time),'yyyy-mm-dd hh:mi:ss(24h)'));

Where the colum date_col and time_col have the data type DATE and TIME, respectively. The date_and_time_text columns is the original date set. However I can not run it as there is a sytax error. What do I do wrong?

Many thanks already in advance for your help :)

Best,
David