Error Code: 1411. Incorrect datetime value: '' for function str_to_date

Question

Im trying to learn how to clean data using MySQL.

I have a column where the value is text and want to change to date. The column has both empty fields and datetime format YYYY-mm-dd hh:mm:ss UTC. I'm trying to update the column to date format YYYY-mm-dd with the empty fields as 0000-00-00.

before	after
2023-04-27 11:31:00 UTC	2023-04-27
	0000-00-00

my query is:

UPDATE table
SET column = date(str_to_date(column, '%Y-%m-%d %H:%i:%s UTC'))
WHERE column IS NOT NULL AND column != ' ';

I get Error Code: 1411. Incorrect datetime value: '' for function str_to_date

But when I try

SELECT
date(str_to_date(column, '%Y-%m-%d %H:%i:%s UTC')) FROM table
WHERE column IS NOT NULL AND column !=' ';

I don't receive an error message. I do get the right date format but still with NULL for the empty fields.

before	after
2023-04-27 11:31:00 UTC	2023-04-27
	NULL