主页 / coding / 问题 / 79163873
Accepted
John Beasley
Asked: 2024-11-07 02:42:23 +0800 CST

转换奇怪的日期时间格式

  • 772

在我的一个 MySQL 5.7 表中，有一列具有奇怪的日期时间格式。它看起来像这样：

2024-08-09 20:57:31.4477

我正在尝试使用此查询将其转换为日期格式 m/d/Y。以下是我目前正在尝试的操作：

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;

但我收到以下错误：

Incorrect datetime value: '2024-08-09 20:57:31.4477' for function str_to_date

当尝试比较日期时间列时，我也使用了这种格式： '%Y-%m-%d %H:%i:%s %p'但我收到了同样的错误。

对于上述日期时间，我应该在 str_to_date 中使用什么格式？

我尝试使用这些格式：

'%Y-%m-%d %H:%i:%s.%P'
'%Y-%m-%d %H:%i:%s.%p'
'%Y-%m-%d %H:%i:%s.%s'

仍然出现同样的错误。

datetime

2 个回答

  1. Best Answer

    该示例日期字符串“2024-08-09 20:57:31.4477”中的最后四位数字是秒的小数部分，即提到了 32.4477 秒，或者说是 32 秒和 447700 微秒。在输入字符串中，表示秒部分有小数部分的正确方法是%f。

    请注意，没有%P说明符，%p说明符涉及 AM/PM。请参阅SQL 5.7 文档中的日期和时间函数。

    因此用作函数'%Y-%m-%d %H:%i:%s.%f'的格式字符串str_to_date。

    • 1

  2. 由于日期时间字段的位长度，我以前也遇到过同样的问题。在设计表时，您应该尽量减少日期时间字段值的大小。如果您不想更改表的设计，可以尝试这个。

    UPDATE table_name 
SET column1 = 
    CASE 
        WHEN column1 = '' THEN NULL 
        ELSE DATE_FORMAT(
            STR_TO_DATE(SUBSTRING_INDEX(column1, '.', 1), '%Y-%m-%d %H:%i:%s'), 
            '%m/%d/%Y'
        )
    END;
    • 1

相关问题