如何在SQL查询中将年月显示为1Y2M？及月份差格式转换

如果是要把日期间隔或者单个日期的年月转换成1Y2M这种紧凑格式，这里有两种常见场景的解决方案：

场景1：计算两个日期的间隔为1Y2M格式 #

先通过MONTHS_BETWEEN获取总月份数，再拆分成年数和剩余月份，最后拼接成目标格式：

SELECT
    FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE), CUR_EFF_DATE)/12) || 'Y' ||
    MOD(ROUND(MONTHS_BETWEEN(TRUNC(SYSDATE), CUR_EFF_DATE)), 12) || 'M' AS year_month_format
FROM xyz;

逻辑解释： #

• FLOOR(MONTHS_BETWEEN(...) /12)：取总月数除以12的整数部分，得到完整年数
• MOD(ROUND(MONTHS_BETWEEN(...)),12)：用ROUND消除小数误差后，取总月数除以12的余数，得到剩余月份
• 用||拼接年数、Y、剩余月份、M，形成XYYZM格式

场景2：从单个日期提取年月并格式化为1Y2M #

比如从当前日期提取年月组合成目标格式：

SELECT
    EXTRACT(YEAR FROM SYSDATE) || 'Y' ||
    EXTRACT(MONTH FROM SYSDATE) || 'M' AS year_month_format
FROM dual;

你的原查询返回的是带小数的年数，咱们可以先回到总月份数来处理，这样能更精准地拆分年和月，同时灵活处理单复数和不同场景的显示：

SELECT
    CASE
        -- 既有年又有月的情况
        WHEN years > 0 AND months > 0 THEN
            years || CASE WHEN years = 1 THEN 'yr ' ELSE 'yrs ' END ||
            months || CASE WHEN months = 1 THEN ' month' ELSE ' months' END
        -- 只有年的情况
        WHEN years > 0 THEN
            years || CASE WHEN years = 1 THEN 'yr' ELSE 'yrs' END
        -- 只有月的情况
        WHEN months > 0 THEN
            months || CASE WHEN months = 1 THEN ' month' ELSE ' months' END
        -- 间隔为0的兜底情况
        ELSE '0 months'
    END AS duration_text
FROM (
    -- 子查询先计算核心数值：年数、剩余月份
    SELECT
        FLOOR(MONTHS_BETWEEN(TRUNC(SYSDATE), CUR_EFF_DATE)/12) AS years,
        MOD(ROUND(MONTHS_BETWEEN(TRUNC(SYSDATE), CUR_EFF_DATE)), 12) AS months
    FROM xyz
);

关键细节说明： #

1. 子查询里用FLOOR取整得到完整年数，用MOD取余得到剩余月份，ROUND是为了避免MONTHS_BETWEEN返回的微小小数误差
2. 外层CASE分支处理所有可能的显示场景：
   • 当同时有年和月时，分别处理年、月的单复数（1用yr/month，大于1用yrs/months）
   • 只有年或只有月时，单独显示对应内容
   • 间隔为0时返回0 months作为兜底

举几个实际效果的例子：

• 总月份为14 → 输出1yr 2 months
• 总月份为1 → 输出1 month
• 总月份为25 → 输出2yrs 1 month
• 总月份为0 → 输出0 months

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