ORA-01861: literal does not match format string ORA-06512: at "SYS.DBMS_SQL", line 1721

Kemushi 0
Viewed 39

I need to create a lookup table of calendar for 10 years starting from 2020-01-01, but keep getting the error from the title. Could anyone tell me how to fix the syntax?

I saw there are similar questions but I still don't know how to fix it...

Thank you in advance.

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')
;
oracle sql
1 Answers

Use virtual columns (then you will never have a situation where your values get out-of-sync and you have two different date values in the same row):

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

Then use a DATE literal:

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

fiddle

MT00
Related