data type in Oracle-CodePudding

how to resolve this?

INSERT INTO logiciel VALUES ('log1','Oracle 6',13-05-1995,'6.2','UNIX','3000');
INSERT INTO logiciel VALUES ('log1','Oracle 6',13-05-1995,'6.2','UNIX','3000')
                                                    *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

the error here's the table the table

 create table logiciel (
    nLog varchar2 (5) primary key,
    nomLog varchar (20) not null,
    dateAchat date,
    version varchar2 (7),
    typeLog varchar2 (9),
    prix number (6,2)
    );

CodePudding user response：

Use date literal or TO_DATE function with appropriate format mask. Do not insert strings into date datatype columns, hoping that Oracle will guess format you used.

SQL> -- date literal is always DATE 'yyyy-mm-dd'
SQL> INSERT INTO logiciel (nlog, nomlog, dateachat, version, typelog, prix)
  2    VALUES ('log1', 'Oracle 6', date '1995-05-13', '6.2', 'UNIX', '3000');

1 row created.

SQL> -- TO_DATE lets you choose format, but then the format mask must follow it
SQL> INSERT INTO logiciel (nlog, nomlog, dateachat, version, typelog, prix)
  2    VALUES ('log2', 'Oracle 6', to_date('13-05-1995', 'dd-mm-yyyy'), '6.2', 'UNIX', '3000');

1 row created.

SQL>

CodePudding user response：

You have to use quotes around your date value as dates are treated as string -

INSERT INTO logiciel VALUES ('log1','Oracle 6','13-05-1995','6.2','UNIX','3000');

But only using string will tell the DB to store it as string not as date. The difference between them is, You can not do any date manipulation on strings. So you have 2 option now.

Use To_Date function with date format -

INSERT INTO logiciel VALUES ('log1','Oracle 6',TO_DATE('13-05-1995', 'DD-MM-YYYY'),'6.2','UNIX','3000');

Use DATE keyword which is supported by ANSI standard but with that, you have to use the date format as 'YYYY-MM-DD'-

INSERT INTO logiciel VALUES ('log1','Oracle 6',DATE '1995-05-13','6.2','UNIX','3000');