MySQL DATE_FORMAT处理%m/%d/%Y返回NULL而%Y/%m/%d正常如何解决

问题根源 #

你混淆了DATE_FORMAT和STR_TO_DATE两个函数的作用：

• DATE_FORMAT的第一个参数要求是合法的日期/时间类型数据，如果你传入字符串，MySQL会尝试隐式转换为日期类型，转换失败就会直接返回NULL
• 你的第二条语句传入的09/17/2021 12:00:00 AM不符合MySQL默认的字符串隐式转日期规则，所以转换直接失败，最终返回NULL
• 第一条语句能正常返回属于巧合，因为2021/09/17 12:00:00 AM刚好符合MySQL的隐式转换规则，不具备通用性。

解决方案 #

先通过STR_TO_DATE将自定义格式的字符串显式转换为MySQL日期类型，再做后续处理即可：

1. 如果只需要返回日期类型的结果，直接执行：

SELECT DATE(STR_TO_DATE('09/17/2021 12:00:00 AM', "%m/%d/%Y %h:%i:%s %p"));

2. 如果需要返回9/17/2021这种不带前导零的字符串格式结果，执行：

SELECT DATE_FORMAT(STR_TO_DATE('09/17/2021 12:00:00 AM', "%m/%d/%Y %h:%i:%s %p"), "%c/%e/%Y");

其中%c对应不带前导零的月份，%e对应不带前导零的日期，符合你的输出要求。

核心规则说明 #

• 所有字符串转日期的操作，都应该显式用STR_TO_DATE(输入字符串, 输入字符串对应的格式模板)处理，不要依赖MySQL的隐式转换，避免出现不兼容问题
• 日期类型转自定义格式字符串的操作，才使用DATE_FORMAT(日期类型数据, 输出格式模板)处理

内容的提问来源于stack exchange，提问作者Jonnel VeXuZ Dorotan