ORA-01481: invalid number format model at to_date

Question

this is my table:

create table exemplare(
    id integer,
    id_tierart integer not null,
    name varchar2(32),
    details varchar2(32),
    geburtsdatum date,
    kaufdatum date,
    
    constraint pk_exemplare_id primary key(id),
    constraint fk_exemplare_id_tierarzt foreign key(id_tierart) references tierarten(id),
    constraint uq_exemplare_name unique(name, id_tierart),
    constraint ck_exemplare_geburtsdatum check(to_char(geburtsdatum, 'mm') < 10),
    -- constraint ck_exemplare_kaufdatum check(to_char(geburtsdatum, 'yyyy') - to_char(kaufdatum, 'yyyy') <= 100),
    constraint ck_exemplare_kaufdatum check(to_char(kaufdatum-geburtsdatum, 'yyyy') <= 100),
    constraint ck_exemplare_id check(MOD(id, 10) = 0)
);

insert into exemplare values(10, 11, 'Hugo', 'bedroht', to_date('13.05.1975', 'dd.mm.yyyy'), to_date('04.07.2018', 'dd.mm.yyyy'));
insert into exemplare values(20, 22, 'Fred', 'nicht bedroht', to_date('13.05.1975', 'dd.mm.yyyy'), to_date('04.07.2018', 'dd.mm.yyyy'));

at the inserts it says:

Fehler beim Start in Zeile: 66 in Befehl -
insert into exemplare values(20, 22, 'Fred', 'nicht bedroht', to_date('13.05.1975', 'dd.mm.yyyy'), to_date('04.07.2018', 'dd.mm.yyyy'))
Fehlerbericht -
ORA-01481: invalid model for number format

it seems to be some kind of issue with the to_date function, but I can't figure it out what exactly is the problem does anyone has an idea why it doesn't work?

score 1 · Accepted Answer · answered Nov 24 '20 at 18:16

I think the problem is this constraint:

constraint ck_exemplare_kaufdatum check(to_char(kaufdatum-geburtsdatum, 'yyyy') <= 100),

We can do arithmetic with dates in Oracle, so this kaufdatum-geburtsdatum valid but the result is numeric. Consequently using a date format mask in the TO_CHAR() is invalid.

Your posted code shows a commented version of the constraint which should be valid. Why not use that instead?

constraint ck_exemplare_kaufdatum check(to_char(geburtsdatum, 'yyyy') - to_char(kaufdatum, 'yyyy') <= 100)

oh thank you it works. I just was confused, because I am allowed to create a table but then the insert does not work, therefore I thought the problem is at the insert. I really appreciate your help — MP13, Nov 24 '20 at 18:43

ORA-01481: invalid number format model at to_date

1 Answers1