How to update empty time in datetime MySQL

We are storing datetime in a column on our MySQL database, formatted in TEXT, but when our datetime is supposed to look like below:

‘xxxx-xx-xx 00:00:00‘

The time is deleted or not show on our datetime, and therefore our datetime, at that specific time, only contains the date:

‘xxxx-xx-xx‘

What we want is first of all to figure out why this is occurring, but for now we need to edit every row, and make sure the datetime is also showing the time. We have tried to change the impacted rows by using this query:

UPDATE table SET TIME(col_datetime) = '00:00:00' WHERE LENGTH(TIME(col_datetime)) = 0;

Above query should update the time on the datetime for col_datetime, where length of time is 0. Unfortunately, we receive an error, and we can’t run the query. This is the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘(time_start) = ’00:00:00′ WHERE LENGTH(TIME(time_start)) = 0’ at line 2

How can we change time on our datetime, where time is not shown?

Answer

Don’t store dates as strings. Instead, you want to use the datetime datatype: it has a time part, that defaults to 00:00:00 when not specified.

Here is a small conversion script for that purpose:

alter table mytable add col_datetime_new datetime;
update mytable set col_datetime_new = col_datetime;
alter table mytable drop col_datetime;
alter table mytable change column col_datetime_new col_datetime datetime;

This leverages the fact that you are using format YYYY-MM-DD in your string dates, so conversion to datetime is seemless.