Str_to_date is showing NULL when date is available

General
1

I was using an UPDATE query to fix my dates. It’s the same query with a CASE statement that I’ve used before. It checks if the VARCHAR date is blank, if so, then set to NULL. Otherwise, change the format of the date.

For some reason, it’s setting all of the dates to NULL.

After re-importing the data, I started using a SELECT (which is probably the better practice). Here is the SELECT:

SELECT `dateColumn`, CASE `dateColumn`
  WHEN '' THEN NULL
  ELSE date_format(str_to_date(`dateColumn`, '%m/%d/%Y %H:%i:%s %P'), '%Y-%m-%d')
  END AS 'NEW DATE'
FROM `table` where `EmailAddress` = 'myemail@yahoo.com';

This gives me these results:

      dateColumn      | NEW DATE
----------------------------------------
9/9/2024 12:00:00 AM  | NULL

The format seems correct. Not sure why it’s changing the date to NULL.

Please help.

** EDIT **

I just noticed that I am getting a warning that reads:

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

Unlikely that is causing the error.