ORA-01861：文字與格式字串不匹配ORA-06512：在“SYS.DBMS

我需要從 2020-01-01 開始創建一個為期 10 年的日歷查找表，但不斷從標題中得到錯誤。誰能告訴我如何修復語法？

我看到有類似的問題，但我仍然不知道如何解決它......

先感謝您。

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
)
;

INSERT INTO LU_DATE
(
    YMD_DATE,
    YMD_ID,
    YW_ID,
    YQ_ID,
    YM_ID,
    Y_ID,
    YMLY_ID
)
SELECT
    TO_DATE('20200101','YYYYMMDD')   rownum -1 AS YMD_DATE,
    TO_NUMBER(TO_CHAR(TO_DATE('20200101')   rownum -1,'YYYYMMDD')) AS YMD_ID,
    TO_NUMBER(TO_CHAR(TO_DATE('20200101')   rownum -1,'YYYY'))*100  TO_NUMBER(TO_CHAR(TO_DATE('20200101')   rownum -1,'YYYYMMDD'),'ww')   AS YW_ID,
    TO_NUMBER(TO_CHAR(TO_DATE('20200101')   rownum -1,'YYYY'))*10   TO_NUMBER(TO_CHAR(TO_DATE('20200101')   rownum -1,'YYYYMMDD'),'q')   AS YQ_ID,
    TO_NUMBER(TO_CHAR(TO_DATE('20200101')   rownum -1,'YYYYMM'))   AS YM_ID,
    TO_NUMBER(TO_CHAR(TO_DATE('20200101')   rownum -1,'YYYY'))   AS Y_ID,
    TO_NUMBER(TO_CHAR(ADD_MONTHS(TO_DATE('20200101')   rownum -1 ,-12),'YYYYMM')) AS YMLY_ID
FROM DUAL
  connect by TO_DATE('20200101')    rownum -1 <= TO_DATE('20301231')
;

uj5u.com熱心網友回復：

使用虛擬列（那么您將永遠不會遇到值不同步并且同一行中有兩個不同日期值的情況）：

CREATE TABLE LU_DATE
(
    YMD_DATE       DATE
                   NOT NULL,
    YMD_ID         NUMBER(8,0)
                   GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(ymd_date, 'YYYYMMDD')))
                   NOT NULL,
    YW_ID          VARCHAR2(6 char)
                   GENERATED ALWAYS AS (TO_CHAR(ymd_date, 'YYYYWW'))
                   NOT NULL,
    YQ_ID          VARCHAR2(5 char)
                   GENERATED ALWAYS AS (TO_CHAR(ymd_date, 'YYYYQ'))
                   NOT NULL,
    YM_ID          NUMBER(6,0)
                   GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(ymd_date, 'YYYYMM')))
                   NOT NULL,
    Y_ID           NUMBER(4,0)
                   GENERATED ALWAYS AS (EXTRACT(YEAR FROM ymd_date))
                   NOT NULL,
    YMLY_ID        NUMBER(6,0)
                   GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(ADD_MONTHS(ymd_date, -12), 'YYYYMM')))
                   NOT NULL
);

然后使用DATE文字：

INSERT INTO LU_DATE(YMD_DATE)
SELECT DATE '2020-01-01'   LEVEL -1
FROM DUAL
connect by DATE '2020-01-01'   LEVEL -1 < DATE '2031-01-01';

小提琴

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/506860.html

標籤：sql 甲骨文

上一篇：Oracle將表列轉換為兩列

下一篇：帶有Oracle資料庫的Django模型回傳錯誤：ORA-00904識別符號無效

ORA-01861：文字與格式字串不匹配ORA-06512：在“SYS.DBMS_SQL”，第1721行