[SOLVED] Convert strange Datetime format

Convert strange Datetime format

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 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.