DATE_FORMAT与STR_TO_DATE函数失效，提取日期返回NULL/0求助

错误原因分析 #

你的脚本存在三个核心问题：

1. DATE_FORMAT直接作用于字符串：如果datetime字段是字符串类型（而非DATE/DATETIME类型），DATE_FORMAT无法直接解析非标准格式的字符串，会返回NULL。
2. STR_TO_DATE格式串不匹配：原日期格式是MM/DD/YYYY HH:MI:SS AM，但你用了%Y作为格式串，完全不匹配原字符串结构，导致转换出无效日期，后续调用DAY()自然返回0。
3. 笛卡尔积连接子查询：两个子查询用逗号连接会生成笛卡尔积，结果行数是表行数的平方，这显然不是你需要的关联查询效果。

正确实现方式 #

要提取日或月，需先通过STR_TO_DATE将字符串转成合法的日期类型，再用日期函数提取对应部分。

基础写法（直接提取）

SELECT
  -- 提取日期（数字格式）
  DAY(STR_TO_DATE(datetime, '%m/%d/%Y %h:%i:%s %p')) AS day_num,
  -- 提取月份（数字格式）
  MONTH(STR_TO_DATE(datetime, '%m/%d/%Y %h:%i:%s %p')) AS month_num,
  -- 提取两位格式的日期
  DATE_FORMAT(STR_TO_DATE(datetime, '%m/%d/%Y %h:%i:%s %p'), '%d') AS day_str,
  -- 提取两位格式的月份
  DATE_FORMAT(STR_TO_DATE(datetime, '%m/%d/%Y %h:%i:%s %p'), '%m') AS month_str
FROM may2023;

优化写法（避免重复转换）

如果需要多次使用转换后的日期，可先在子查询中完成转换，提升效率：

SELECT
  DAY(date_val) AS day_num,
  MONTH(date_val) AS month_num,
  DATE_FORMAT(date_val, '%d') AS day_str,
  DATE_FORMAT(date_val, '%m') AS month_str
FROM (
  SELECT STR_TO_DATE(datetime, '%m/%d/%Y %h:%i:%s %p') AS date_val
  FROM may2023
) AS converted_dates;

格式串说明 #

格式串的每个部分必须和原日期字符串完全对应：

• %m：两位数字的月份（01-12）
• %d：两位数字的日期（01-31）
• %Y：四位数字的年份
• %h：12小时制的小时（01-12）
• %i：两位数字的分钟（00-59）
• %s：两位数字的秒（00-59）
• %p：AM/PM标识

特殊情况说明 #

如果你的datetime字段本身就是DATE/DATETIME类型，无需转换，直接提取即可：

SELECT
  DAY(datetime) AS day_num,
  MONTH(datetime) AS month_num,
  DATE_FORMAT(datetime, '%d') AS day_str,
  DATE_FORMAT(datetime, '%m') AS month_str
FROM may2023;

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