Oracle SQL日期添加天数间隔异常：仅周三生效问题排查

问题描述 #

我需要在Oracle SQL中将一组日期调整至下周周一，但使用date + N的方式时，只有周三的日期能正确计算出新日期，其余日期的new_dt字段都是空值。这是哪里出错了？是Oracle SQL Developer的bug吗？

原SQL代码 #

WITH data AS (
    SELECT '29-JAN-23' dt FROM dual UNION
    SELECT '30-JAN-23' dt FROM dual UNION
    SELECT '31-JAN-23' dt FROM dual UNION
    SELECT '01-FEB-23' dt FROM dual UNION
    SELECT '02-FEB-23' dt FROM dual UNION
    SELECT '03-FEB-23' dt FROM dual UNION
    SELECT '04-FEB-23' dt FROM dual UNION
    SELECT '05-FEB-23' dt FROM dual UNION
    SELECT '06-FEB-23' dt FROM dual UNION
    SELECT '07-FEB-23' dt FROM dual UNION
    SELECT '08-FEB-23' dt FROM dual UNION
    SELECT '09-FEB-23' dt FROM dual
)

SELECT
    TO_DATE(dt) dt,
    TO_CHAR(TO_DATE(dt),'DAY') dt_day,
    CASE
        WHEN TO_CHAR(TO_DATE(dt),'DAY') = 'MONDAY'      THEN TO_DATE(dt)
        WHEN TO_CHAR(TO_DATE(dt),'DAY') = 'TUSEDAY'     THEN TO_DATE(dt) + 6
        WHEN TO_CHAR(TO_DATE(dt),'DAY') = 'WEDNESDAY'   THEN TO_DATE(dt) + 5
        WHEN TO_CHAR(TO_DATE(dt),'DAY') = 'THURSDAY'    THEN TO_DATE(dt) + 4
        WHEN TO_CHAR(TO_DATE(dt),'DAY') = 'FRIDAY'      THEN TO_DATE(dt) + 3
        WHEN TO_CHAR(TO_DATE(dt),'DAY') = 'SATURDAY'    THEN TO_DATE(dt) + 2
        WHEN TO_CHAR(TO_DATE(dt),'DAY') = 'SUNDAY'      THEN TO_DATE(dt) + 1
    END new_dt
FROM data
ORDER BY TO_DATE(dt);

原输出结果 #

错误原因 #

1. 拼写错误：TUSEDAY是错误拼写，正确应为TUESDAY，导致周二的条件永远无法匹配。
2. 字符串空格填充：Oracle的TO_CHAR(date, 'DAY')函数返回的字符串会自动补空格到固定长度（比如英文环境下为9个字符），例如'SUNDAY'实际返回的是'SUNDAY '（后面带空格），直接用等号=匹配时，因为字符串长度不一致，除了WEDNESDAY刚好是9个字符外，其他日期的条件都不成立，导致new_dt为空。

修复方案 #

方案1：修正拼写并去除空格 #

使用TRIM()函数去掉TO_CHAR返回值的空格，同时修正拼写错误：

WITH data AS (
    SELECT '29-JAN-23' dt FROM dual UNION
    SELECT '30-JAN-23' dt FROM dual UNION
    SELECT '31-JAN-23' dt FROM dual UNION
    SELECT '01-FEB-23' dt FROM dual UNION
    SELECT '02-FEB-23' dt FROM dual UNION
    SELECT '03-FEB-23' dt FROM dual UNION
    SELECT '04-FEB-23' dt FROM dual UNION
    SELECT '05-FEB-23' dt FROM dual UNION
    SELECT '06-FEB-23' dt FROM dual UNION
    SELECT '07-FEB-23' dt FROM dual UNION
    SELECT '08-FEB-23' dt FROM dual UNION
    SELECT '09-FEB-23' dt FROM dual
)

SELECT
    TO_DATE(dt) dt,
    TO_CHAR(TO_DATE(dt),'DAY') dt_day,
    CASE
        WHEN TRIM(TO_CHAR(TO_DATE(dt),'DAY')) = 'MONDAY'    THEN TO_DATE(dt) + 7  -- 若需求是下周周一，周一需+7；原代码返回当前日期，可按需调整
        WHEN TRIM(TO_CHAR(TO_DATE(dt),'DAY')) = 'TUESDAY'   THEN TO_DATE(dt) + 6
        WHEN TRIM(TO_CHAR(TO_DATE(dt),'DAY')) = 'WEDNESDAY' THEN TO_DATE(dt) + 5
        WHEN TRIM(TO_CHAR(TO_DATE(dt),'DAY')) = 'THURSDAY'  THEN TO_DATE(dt) + 4
        WHEN TRIM(TO_CHAR(TO_DATE(dt),'DAY')) = 'FRIDAY'    THEN TO_DATE(dt) + 3
        WHEN TRIM(TO_CHAR(TO_DATE(dt),'DAY')) = 'SATURDAY'  THEN TO_DATE(dt) + 2
        WHEN TRIM(TO_CHAR(TO_DATE(dt),'DAY')) = 'SUNDAY'    THEN TO_DATE(dt) + 1
    END new_dt
FROM data
ORDER BY TO_DATE(dt);

方案2：使用FM格式修饰符 #

FM修饰符可以去掉TO_CHAR的空格填充，写法更简洁：

WITH data AS (
    SELECT '29-JAN-23' dt FROM dual UNION
    SELECT '30-JAN-23' dt FROM dual UNION
    SELECT '31-JAN-23' dt FROM dual UNION
    SELECT '01-FEB-23' dt FROM dual UNION
    SELECT '02-FEB-23' dt FROM dual UNION
    SELECT '03-FEB-23' dt FROM dual UNION
    SELECT '04-FEB-23' dt FROM dual UNION
    SELECT '05-FEB-23' dt FROM dual UNION
    SELECT '06-FEB-23' dt FROM dual UNION
    SELECT '07-FEB-23' dt FROM dual UNION
    SELECT '08-FEB-23' dt FROM dual UNION
    SELECT '09-FEB-23' dt FROM dual
)

SELECT
    TO_DATE(dt) dt,
    TO_CHAR(TO_DATE(dt),'FMDAY') dt_day,
    CASE
        WHEN TO_CHAR(TO_DATE(dt),'FMDAY') = 'MONDAY'    THEN TO_DATE(dt) +7
        WHEN TO_CHAR(TO_DATE(dt),'FMDAY') = 'TUESDAY'   THEN TO_DATE(dt) +6
        WHEN TO_CHAR(TO_DATE(dt),'FMDAY') = 'WEDNESDAY' THEN TO_DATE(dt) +5
        WHEN TO_CHAR(TO_DATE(dt),'FMDAY') = 'THURSDAY'  THEN TO_DATE(dt) +4
        WHEN TO_CHAR(TO_DATE(dt),'FMDAY') = 'FRIDAY'    THEN TO_DATE(dt) +3
        WHEN TO_CHAR(TO_DATE(dt),'FMDAY') = 'SATURDAY'  THEN TO_DATE(dt) +2
        WHEN TO_CHAR(TO_DATE(dt),'FMDAY') = 'SUNDAY'    THEN TO_DATE(dt) +1
    END new_dt
FROM data
ORDER BY TO_DATE(dt);

方案3：使用NEXT_DAY函数（最简洁） #

Oracle提供NEXT_DAY函数直接获取下一个指定星期几的日期，无需手动计算间隔：

WITH data AS (
    SELECT '29-JAN-23' dt FROM dual UNION
    SELECT '30-JAN-23' dt FROM dual UNION
    SELECT '31-JAN-23' dt FROM dual UNION
    SELECT '01-FEB-23' dt FROM dual UNION
    SELECT '02-FEB-23' dt FROM dual UNION
    SELECT '03-FEB-23' dt FROM dual UNION
    SELECT '04-FEB-23' dt FROM dual UNION
    SELECT '05-FEB-23' dt FROM dual UNION
    SELECT '06-FEB-23' dt FROM dual UNION
    SELECT '07-FEB-23' dt FROM dual UNION
    SELECT '08-FEB-23' dt FROM dual UNION
    SELECT '09-FEB-23' dt FROM dual
)

SELECT
    TO_DATE(dt) dt,
    TO_CHAR(TO_DATE(dt),'FMDAY') dt_day,
    NEXT_DAY(TO_DATE(dt), 'MONDAY') new_dt
FROM data
ORDER BY TO_DATE(dt);

注意：NEXT_DAY的星期参数依赖会话的NLS_DATE_LANGUAGE设置，若要避免语言问题，可使用数字指定（比如NEXT_DAY(dt, 2)，1代表周日、2代表周一，具体取决于NLS_TERRITORY），或用NEXT_DAY(dt, TO_DATE('01', 'D'))获取会话默认的周一。

修复后输出示例 #

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