如何使用SQL生成两个日期之间的月份列表？

通用递归CTE实现（以PostgreSQL为例） #

递归CTE是生成连续日期序列的常用方案，以下查询可以直接生成指定日期范围内每个月的第一天：

WITH RECURSIVE month_list AS (
    -- 锚点：取起始日期所在月份的第一天
    SELECT DATE_TRUNC('month', '2021-01-01'::DATE) AS month_start
    UNION ALL
    -- 递归：每次给当前月份加1个月，保持取当月第一天
    SELECT DATE_TRUNC('month', month_start + INTERVAL '1 month')
    FROM month_list
    -- 终止条件：当前月份未超过结束日期所在月份
    WHERE month_start < DATE_TRUNC('month', '2021-06-01'::DATE)
)
-- 格式化输出为日期类型
SELECT month_start::DATE AS month_date
FROM month_list
ORDER BY month_start;

不同SQL方言的适配调整 #

如果使用其他数据库，只需替换日期处理函数即可：

MySQL

WITH RECURSIVE month_list AS (
    SELECT DATE_FORMAT('2021-01-01', '%Y-%m-01') AS month_start
    UNION ALL
    SELECT DATE_FORMAT(DATE_ADD(month_start, INTERVAL 1 MONTH), '%Y-%m-01')
    FROM month_list
    WHERE month_start < DATE_FORMAT('2021-06-01', '%Y-%m-01')
)
SELECT month_start
FROM month_list
ORDER BY month_start;

SQL Server

WITH RECURSIVE month_list AS (
    SELECT DATEFROMPARTS(YEAR('2021-01-01'), MONTH('2021-01-01'), 1) AS month_start
    UNION ALL
    SELECT DATEADD(MONTH, 1, month_start)
    FROM month_list
    WHERE month_start < DATEFROMPARTS(YEAR('2021-06-01'), MONTH('2021-06-01'), 1)
)
SELECT month_start
FROM month_list
ORDER BY month_start;

关键注意事项 #

• 日期对齐：使用DATE_TRUNC/DATE_FORMAT/DATEFROMPARTS确保每次生成的都是当月第一天，避免因月份天数差异导致的日期偏移（比如1月31日加1个月会变成2月28日，用这些函数可以修正为2月1日）。
• 递归深度限制：多数数据库默认递归深度上限为1000，如果日期跨度超过1000个月，需要调整对应参数（比如PostgreSQL执行SET max_recursion_depth = 10000;）。
• 灵活调整范围：如果需要包含结束日期之后的月份，只需将终止条件的<改为<=即可。

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