Oracle SQL：将DD-MON日期转为带合适年份的DD-MM-YYYY格式

需求说明 #

将DD-MON格式的字符串日期（如07-Jul、08-Oct）转换为DD-MM-YYYY格式，年份规则如下：

• 若目标月份晚于当前系统月份，年份取当前年份
• 若目标月份早于或等于当前系统月份，年份取当前年份的下一年

实现方案 #

以下提供两种可行的SQL写法，均考虑了环境兼容性（避免月份缩写识别问题）：

写法一：CASE条件判断拼接年份

WITH sample_dates AS (
    SELECT '07-Jul' AS date_str FROM dual
    UNION ALL
    SELECT '08-Oct' AS date_str FROM dual
)
SELECT
    date_str,
    TO_CHAR(
        CASE
            WHEN EXTRACT(MONTH FROM TO_DATE(date_str, 'DD-MON', 'NLS_DATE_LANGUAGE=ENGLISH')) > EXTRACT(MONTH FROM SYSDATE)
                THEN TO_DATE(date_str || '-' || EXTRACT(YEAR FROM SYSDATE), 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=ENGLISH')
            ELSE TO_DATE(date_str || '-' || (EXTRACT(YEAR FROM SYSDATE) + 1), 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=ENGLISH')
        END,
        'DD-MM-YYYY'
    ) AS formatted_date
FROM sample_dates;

写法二：ADD_MONTHS简化年份计算

WITH sample_dates AS (
    SELECT '07-Jul' AS date_str FROM dual
    UNION ALL
    SELECT '08-Oct' AS date_str FROM dual
)
SELECT
    date_str,
    TO_CHAR(
        ADD_MONTHS(
            TO_DATE(date_str, 'DD-MON', 'NLS_DATE_LANGUAGE=ENGLISH'),
            12 * CASE WHEN EXTRACT(MONTH FROM TO_DATE(date_str, 'DD-MON', 'NLS_DATE_LANGUAGE=ENGLISH')) <= EXTRACT(MONTH FROM SYSDATE) THEN 1 ELSE 0 END
        ),
        'DD-MM-YYYY'
    ) AS formatted_date
FROM sample_dates;

关键细节说明 #

1. NLS_DATE_LANGUAGE参数：指定NLS_DATE_LANGUAGE=ENGLISH确保英文月份缩写（如Jul、Oct）能被Oracle正确识别，避免因系统语言环境不同导致的转换错误。
2. 月份比较逻辑：通过EXTRACT(MONTH ...)提取目标日期和当前系统日期的月份数值进行对比，判断是否需要切换到下一年。
3. 无效输入处理：如果存在格式不合法的输入字符串，可以结合VALIDATE_CONVERSION函数提前过滤或处理，避免TO_DATE抛出异常。

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