如何使用无存储过程的SQL获取近n个月每个月的首日和末日

以下是不同主流数据库的原生SQL实现，均不依赖存储过程，可直接运行，示例取最近5个已结束的完整自然月的首日、末日，你可按需调整月份参数：

SELECT
    DATE_FORMAT(date_sub(curdate(), INTERVAL (t.n) MONTH), '%d-%m-%Y') AS first_day,
    DATE_FORMAT(LAST_DAY(date_sub(curdate(), INTERVAL (t.n) MONTH)), '%d-%m-%Y') AS last_day
FROM (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) t
ORDER BY first_day ASC;

若需要调整月份数，增减子查询SELECT ... UNION ALL的行数即可。

SELECT
    TO_CHAR(date_trunc('month', CURRENT_DATE - (n || ' month')::interval), 'dd-mm-yyyy') AS first_day,
    TO_CHAR((date_trunc('month', CURRENT_DATE - (n || ' month')::interval) + INTERVAL '1 month - 1 day'), 'dd-mm-yyyy') AS last_day
FROM generate_series(1,5) AS t(n)
ORDER BY first_day ASC;

若需要调整月份数，修改generate_series(1,5)里的第二个参数即可。

SELECT
    FORMAT(DATEADD(month, DATEDIFF(month, 0, GETDATE()) - n, 0), 'dd-MM-yyyy') AS first_day,
    FORMAT(EOMONTH(DATEADD(month, -n, GETDATE())), 'dd-MM-yyyy') AS last_day
FROM (VALUES (1),(2),(3),(4),(5)) AS t(n)
ORDER BY first_day ASC;

若需要调整月份数，增减VALUES后的数值项即可。

内容的提问来源于stack exchange，提问作者Another Day Another Data