mysqldatetimemysql-5.7str-to-date

Incorrect datetime value: ' ' for function str_to_date

In MySQL 5.7, I have a varchar column that has a string datetime that looks like this:

`2024-09-03 02:00:07`

I can run this query:

select date_format(str_to_date(`column1`, '%Y-%m-%d %H:%i:%s'), '%m/%d/%Y')  from table;

This returns the column1 dates in a format that looks like this:

09/03/2024

However when I run this update query:

update table set `column1` = date_format(str_to_date(`column1`, '%Y-%m-%d %H:%i:%s'), '%m/%d/%Y');

I am getting the below error:

Incorrect datetime value: '' for function str_to_date

There are some null values in the column. I updated the structure of the table to DEFAULT: NULL on the column, and I checked the Null checkbox:

enter image description here

How can I fix this so that it will update accordingly?

Solution

  • Check for a blank value before calling the conversion functions.

    update table set `column1` = 
    CASE column1
        WHEN '' THEN NULL 
        ELSE date_format(str_to_date(`column1`, '%Y-%m-%d %H:%i:%s'), '%m/%d/%Y')
    END;