Convert strange Datetime format

November 6, 2024

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.

>Solution :

The correct way to indicate you’ve microseconds in your input string, is %f. Note there is no %P, and %p 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.