what should i do i keep getting ORA-01861: literal does not match format string ORA-06512: at "-CodePudding

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

what shoud i do?

create table TASK (
    TASK_ID number not null constraint task_pk primary key,
    TASK_DESCRIP varchar2(50) not null,
    TASK_STARTING_DATE date not null,
    TASK_ENDING_DATE date not null,
    NUMBER_OF_EMPLOYEE number not null
);

INSERT INTO TASK VALUES(1101,'Initial interview','2018-03-01','2018-03-06','3');

CodePudding user response：

You are simply missing the keyword DATE.

In your insert statement you have '2018-03-01'. This is a string literal, but the table column that is to receive this value is of data type DATE. This means Oracle must convert the string to a date. Now '2018-03-01' is not a date format that is commonly used in your country / region, so Oracle doesn't know how to safely convert it (e.g. how to decide which is day and which is month).

Use a date literal instead. For this we must use the keyword DATE followed by the format yyyy-mm-dd, which is what you are probably already using. Hence:

INSERT INTO task
  (task_id, task_descrip, task_starting_date, task_ending_date, number_of_employee)
  VALUES(1101, 'Initial interview', DATE '2018-03-01', DATE '2018-03-06', 3);

CodePudding user response：

You need to provide the date format your date should have. You can use TO_DATE for that. As example:

INSERT INTO TASK VALUES(1101,'Initial interview',
TO_DATE('2018-03-01','YYYY-DD-MM'),
TO_DATE('2018-03-06','YYYY-DD-MM'),3);

If your date should be formatted the other way, just write 'YYYY-MM-DD' instead of 'YYYY-DD-MM'. See here please: db<>fiddle