In one of my MySQL 5.7 tables, I have column that has a strange datetime format. It looks like this:
2024-08-09 20:57:31.4477
I am trying to use this query to convert it to a date format m/d/Y. Here is what I am currently trying:
update table set `column1` =
CASE column1
WHEN '' THEN NULL
ELSE date_format(str_to_date(`column1`, '%Y-%m-%d %H:%i:%s %P'), '%m/%d/%Y')
END;
But I am getting the below error:
Incorrect datetime value: '2024-08-09 20:57:31.4477' for function str_to_date
When trying to compare the datetime column, I also used this format: '%Y-%m-%d %H:%i:%s %p' but I am getting the same error.
What is the format I should be using in str_to_date for the datetime above?
I tried to use these formats:
'%Y-%m-%d %H:%i:%s.%P'
'%Y-%m-%d %H:%i:%s.%p'
'%Y-%m-%d %H:%i:%s.%s'
Still getting the same error.
The final four digits in that example date string "2024-08-09 20:57:31.4477" are the fractional part of the seconds, i.e. there are 32.4477 seconds mentioned, or otherwise put 32 seconds and 447700 microseconds. The correct way to indicate you've a fractional part for the seconds component in your input string, is %f.
Note there is no %P specifier, and the %p specifier concerns AM/PM. See SQL 5.7 docs on date and time functions.
So use '%Y-%m-%d %H:%i:%s.%f' as format string for the str_to_date function.