MySQL：根据'MMM YYYY'格式计算月份首尾日期的正确方法

报错原因 #

你的SQL执行报错，是因为MySQL当前会话的lc_time_names参数未设置为英文locale，导致STR_TO_DATE无法识别'Sep'、'Jul'这类英文月份缩写。

正确实现方式 #

1. 先设置会话时间区域为英文（单次执行即可）

SET lc_time_names = 'en_US';

2. 生成起始/结束日期

直接使用以下语句即可得到YYYY-MM-dd格式的日期结果：

-- 当月起始日期（YYYY-MM-dd）
DATE(STR_TO_DATE(delivery, '%b %Y')) AS startdate,
-- 当月结束日期（YYYY-MM-dd）
LAST_DAY(STR_TO_DATE(delivery, '%b %Y')) AS enddate

如果需要将结果以字符串类型返回（而非DATE类型），可以用DATE_FORMAT格式化：

DATE_FORMAT(STR_TO_DATE(delivery, '%b %Y'), '%Y-%m-%d') AS startdate,
DATE_FORMAT(LAST_DAY(STR_TO_DATE(delivery, '%b %Y')), '%Y-%m-%d') AS enddate

验证示例 #

针对字段值'Sep 2021'，上述语句会返回：

• startdate: 2021-09-01
• enddate: 2021-09-30

无locale依赖的替代方案 #

如果无法修改会话参数，可使用月份映射的方式（效率略低，但无需设置locale）：

-- 起始日期
STR_TO_DATE(
  CONCAT(
    ELT(FIELD(LEFT(delivery,3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),1,2,3,4,5,6,7,8,9,10,11,12),
    '-01-',
    RIGHT(delivery,4)
  ),
  '%m-%d-%Y'
) AS startdate,
-- 结束日期
LAST_DAY(
  STR_TO_DATE(
    CONCAT(
      ELT(FIELD(LEFT(delivery,3),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec'),1,2,3,4,5,6,7,8,9,10,11,12),
      '-01-',
      RIGHT(delivery,4)
    ),
    '%m-%d-%Y'
  )
) AS enddate

优先推荐第一种设置locale的方案，更简洁高效。

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