MySQL中将VARCHAR格式日期转换为DATETIME时遇错误求助

错误原因分析 #

你遇到的报错是因为STR_TO_DATE的格式符与处理后的字符串完全不匹配：

1. 处理后字符串用横杠-分隔日期部分，但你指定的格式符是%d.%m.%Y（点.分隔），分隔符不匹配；
2. 原始日期是月/日/年格式（比如4/12/2016指4月12日），但你用了%d.%m.%Y（日.月.年）的解析顺序，逻辑完全颠倒；
3. 直接删除AM/PM标识，丢失了12小时制转24小时制的关键信息，导致12:00:00这类时间无法被正确解析。

正确解决方案 #

无需手动替换分隔符和AM/PM，直接用STR_TO_DATE识别原始格式即可，一步到位更可靠：

1. 新增DATETIME类型列（如果还未执行）：

ALTER TABLE hourly_calories ADD date_new DATETIME;

2. 执行转换，原始格式MM/DD/YYYY HH:MI:SS AM/PM对应的MySQL格式符为%m/%d/%Y %h:%i:%s %p：

UPDATE hourly_calories
SET date_new = STR_TO_DATE(ActivityHour, '%m/%d/%Y %h:%i:%s %p');

可选：若要修改原ActivityHour列（不推荐） #

如果一定要将原VARCHAR列转为标准DATETIME格式的字符串，可先转换为DATETIME类型再转回字符串：

-- 先将原列内容转成DATETIME存入新列
UPDATE hourly_calories
SET date_new = STR_TO_DATE(ActivityHour, '%m/%d/%Y %h:%i:%s %p');

-- 再将新列的标准格式写回原列（需确保原列长度足够）
UPDATE hourly_calories
SET ActivityHour = DATE_FORMAT(date_new, '%Y-%m-%d %H:%i:%s');

内容的提问来源于stack exchange，提问作者Ruzilia