[SOLVED] str_to_date is showing NULL when date is available

str_to_date is showing NULL when date is available

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.

Solution

There is no uppercase %P the documentation states lowercase %p for am/pm. str_to_date will return null if it can't parse the format.

Also since you have AM/PM hours you should use %h which is 01 to 12.

I also changed the case syntax a bit.

SELECT dateColumn,
CASE when dateColumn = '' THEN NULL
  ELSE date_format(str_to_date(dateColumn, '%m/%d/%Y %h:%i:%s %p'), '%Y-%m-%d')
  END AS "NEW DATE"
FROM (select '09/09/2024 12:00:00 AM' as dateColumn) a;

See it working here: https://sqlfiddle.com/mysql/online-compiler?id=54541e8d-2bd1-43bc-93b9-e62a511aab0a