I have multiple columns with time values in string format(e.g 2:00 PM) that I want to convert to datetime(e.g 14:00). I have used this code example SELECT STR_TO_DATE(myColumnOne, %H:%i'), STR_TO_DATE(myColumnTwo, %H:%i') FROM myTableName; The problem is that I have some blank values in myColumns which are being converted to 00:00. This will cause problems later as they'll be recognized as midnight time(i.e 12:00 AM). How can I maintain the values to be blank or null after the conversion? Plus: I have some actual, genuine times that are like 12:00 AM which are supposed to be converted to 00:00

CodePudding user response：

SELECT STR_TO_DATE(NULLIF(time_as_string, ''), '%h:%i %p')
FROM data_table

NULLIF() converts "blank values" (empty string) to NULL.

STR_TO_DATE() converts the string value to TIME datatype value.