CODEWITHSUNDEEP
mysqldatetimemysql-5.7str-to-date
John Beasley
John Beasley

Reputation: 3065

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?

Upvotes: 0

Views: 72

Answers (1)

Barmar
Barmar

Reputation: 780655

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;

Upvotes: 1

Related Questions