Update throwing warning instead of error and continues to run

In my previous question (str_to_date is showing NULL when date is available), I was trying to update a VARCHAR datetime to a date format.

This is the update query that I am using:

UPDATE `table` SET `dateColumn` = CASE 
  WHEN `dateColumn` = '' THEN NULL 
  ELSE date_format(str_to_date(`dateColumn`, '%m-%d-%Y %h:%i:%s %p'), '%Y-%m-%d') END;

The above query should look for a date in the dateColumn. If the date is blank, then set it to NULL. Otherwise, change the date format.

What I noticed is that if the dateColumn doesn't match the format that I specified, instead of stopping the query and producing an error, the query spits out a warning that reads this:

Warning: #1411 Incorrect datetime value: '9/9/2024 12:00:00 AM' for function str_to_date

I understand the format is wrong because there are slashes instead of dashes.

The problem is, when I receive the above warning, the query continues to run but changes all of the dates, whether blank or not, to NULL.

I do not want the query to mark all of the dates to NULL. Just the blank dates. This is not happening for some reason, and when this happens, I have to truncate the data and import it again and start over.

What happened to the query stopping on the error?

Why am I getting a warning instead?

Why is the query changing the all of the dates to NULL?

Do I need to update my query so it doesn't change all of the dates to NULL? If so, how?

Solution

Read https://dev.mysql.com/doc/refman/8.4/en/date-and-time-functions.html#function_str-to-date:

If the date, time, or datetime value extracted from str cannot be parsed according to the rules followed by the server, STR_TO_DATE() returns NULL and produces a warning.

(emphasis mine)

It does not say it stops the query or produces an error.

It changes the dates to NULL because you gave STR_TO_DATE() a format that doesn't match the data. I.e. the difference between dashes and slashes is significant.

Demo:

mysql> select date_format(str_to_date(`dateColumn`, '%m-%d-%Y %h:%i:%s %p'), '%Y-%m-%d') as d 
  from (values row('9/9/2024 12:00:00 AM')) AS t(dateColumn);
+------+
| d    |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

Warning (Code 1411): Incorrect datetime value: '9/9/2024 12:00:00 AM' for function str_to_date


mysql> select date_format(str_to_date(`dateColumn`, '%m-%d-%Y %h:%i:%s %p'), '%Y-%m-%d') as d 
  from (values row('9-9-2024 12:00:00 AM')) AS t(dateColumn);
+------------+
| d          |
+------------+
| 2024-09-09 |
+------------+

As the manual describes, when it can't parse the data according to the format you told it, it returns NULL.

You might want to program defensively, and apply expressions only to data with matching format:

UPDATE `table` SET `dateColumn` = CASE 
  WHEN `dateColumn` = '' 
    THEN NULL 
  WHEN STR_TO_DATE(`dateColumn`, '%m-%d-%Y %h:%i:%s %p') IS NOT NULL
    THEN DATE(STR_TO_DATE(`dateColumn`, '%m-%d-%Y %h:%i:%s %p')
  WHEN STR_TO_DATE(`dateColumn`, '%m/%d/%Y %h:%i:%s %p') IS NOT NULL
    THEN DATE(STR_TO_DATE(`dateColumn`, '%m/%d/%Y %h:%i:%s %p')
  ELSE `dateColumn`
  END;

In this example, the ELSE clause is a no-op; it returns the original value of dateColumn, because none of the other cases matched.

This leaves the value in a format that might not be a valid date, but at least it hasn't been lost. You can try to fix the stragglers one by one, instead of having to re-import all the data.