R.co R.co - 7 months ago 18
SQL Question

Merging Date and Time columns into DATETIME column in MySQL

I'm trying to concat two columns

DATE
and
TIME
into one column
DATETIME
.

These columns are already existing but they are in different tables.

The structure is like :

TABLE 1 :
column_datetime (YYYY-MM-DD HH:MM:SS)


TABLE 2 :
column_date (YYYY-MM-DD), column_time(HH-MM-SS).


I need to update the table 1 and I tried this :

UPDATE table1 SET column_datetime = CONCAT('table2.column_date', ' ', 'table2.column_time');


But it says :


Incorrect datetime value : 'table2.column_date table2.column_time for
column 'column_datetime' at row 1.

Answer

Try this query:

UPDATE table1, table2
SET column_datetime = STR_TO_DATE(CONCAT(table2.column_date, ' ', table2.column_time), '%Y-%c-%e %T')
WHERE table1.FK_ID = table2.ID ;