创建2020-2030日历查找表遇ORA-01861错误，求语法修正方案

错误原因 #

1. 日期格式不匹配：多处TO_DATE('20200101')未显式指定格式模型，Oracle会依赖会话默认的NLS_DATE_FORMAT解析字符串，若默认格式不是YYYYMMDD就会触发ORA-01861错误。
2. 字段类型不兼容：YW_ID和YQ_ID定义为VARCHAR类型，但SELECT语句中计算结果是NUMBER，插入时会出现类型不匹配问题。
3. 冗余计算：重复计算TO_DATE('20200101') + rownum -1，既不高效也容易出错。

修正后的完整代码 #

CREATE TABLE LU_DATE
(
    YMD_DATE       DATE                NOT NULL,
    YMD_ID         NUMBER(8,0)         NOT NULL,
    YW_ID          VARCHAR(7 char)     NOT NULL,
    YQ_ID          VARCHAR(6 char)     NOT NULL,
    YM_ID          NUMBER(6,0)         NOT NULL,
    Y_ID           NUMBER(4,0)         NOT NULL,
    YMLY_ID        NUMBER(6,0)         NOT NULL
)
;

CREATE INDEX LU_DATEIAX ON LU_DATE(YMD_DATE ASC);
CREATE INDEX LU_DATEIBX ON LU_DATE(YMD_ID ASC);

WITH date_seq AS (
    SELECT TO_DATE('20200101', 'YYYYMMDD') + LEVEL - 1 AS curr_date
    FROM DUAL
    CONNECT BY TO_DATE('20200101', 'YYYYMMDD') + LEVEL - 1 <= TO_DATE('20301231', 'YYYYMMDD')
)
INSERT INTO LU_DATE
(
    YMD_DATE,
    YMD_ID,
    YW_ID,
    YQ_ID,
    YM_ID,
    Y_ID,
    YMLY_ID
)
SELECT
    curr_date AS YMD_DATE,
    TO_NUMBER(TO_CHAR(curr_date, 'YYYYMMDD')) AS YMD_ID,
    -- 转换为VARCHAR类型，格式为YYYYWW
    TO_CHAR(curr_date, 'YYYYWW') AS YW_ID,
    -- 转换为VARCHAR类型，格式为YYYYQ
    TO_CHAR(curr_date, 'YYYYQ') AS YQ_ID,
    TO_NUMBER(TO_CHAR(curr_date, 'YYYYMM')) AS YM_ID,
    TO_NUMBER(TO_CHAR(curr_date, 'YYYY')) AS Y_ID,
    TO_NUMBER(TO_CHAR(ADD_MONTHS(curr_date, -12), 'YYYYMM')) AS YMLY_ID
FROM date_seq;

关键修正点 #

• 所有日期转换都显式指定'YYYYMMDD'格式模型，避免依赖会话默认设置。
• 使用CTE（date_seq）生成日期序列，只计算一次当前日期，减少冗余计算。
• YW_ID和YQ_ID直接用TO_CHAR生成符合字段类型的字符串（格式分别为YYYYWW和YYYYQ），解决类型不匹配问题。
• 用LEVEL代替ROWNUM生成序列，Oracle中CONNECT BY LEVEL是生成连续序列的标准写法。

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