Merge date from one datetime and time from another datetime

Question

I'm maintaining a database where the date and the time of events are seperated, both are saved as datetime though.

So How do I pull the date from the "date" Column, and the time from the "time" column and insert it into the "datetime"-column

Answer 1

You can use the DATE_FORMAT function in your selects.

For the date:

'%Y-%m-%d'

For time:

'%H:%i:%s'

So something like:

UPDATE `table`
SET `datetimecol` = CONCAT_WS(' ', DATE_FORMAT(`datecol`, '%Y-%m-%d'), DATE_FORMAT(`timecol`, '%H:%i:%s'))