CODEWITHSUNDEEP
oracle10g
Shubham Kumar
Shubham Kumar

Reputation: 53

ORA-01861: literal does not match format string 'date datatype error'

create table Coordinate_with
(
  cor_id char(6),
  cmp_id char(6),
  start_date date,      
  shifted varchar(10),  
  primary key (cor_id,cmp_id),
  foreign key (cor_id) references Coordinator(cor_id),
  foreign key (cmp_id) references Company(cmp_id)
);

insert into Coordinate_with
values ('COR-01','CMP-02','2009-03-22','Morning');

Throwing Error ORA-01861: literal does not match format string.

Upvotes: 1

Views: 12340

Answers (1)

user330315
user330315

Reputation:

When you supply a "plain" string the value is converted using implicit type conversion based on your NLS setting. A (standard) date literal needs the keyword date followed by a string formatted in ISO style (yyyy-mm-dd):

insert into Coordinate_with 
  (cor_id, cmp_id, start_date, shifted)
values 
  ('COR-01','CMP-02', DATE '2009-03-22','Morning');

Alternatively you can use the to_date() function:

insert into Coordinate_with 
  (cor_id, cmp_id, start_date, shifted)
values 
  ('COR-01','CMP-02', to_date('2009-03-22', 'yyyy-mm-dd','Morning');



Btw: work_shift might have been a better column name for shifted. I told you, that you will need to find a name that matches what the column contains.

Upvotes: 5

Related Questions