CODEWITHSUNDEEP
oracle-database
Sravya Varshini
Sravya Varshini

Reputation: 71

Unable to insert date data into oracle table

Unable to insert date data into oracle table. Please find the below query and error

Query:

INSERT INTO TEST.SUPPLIER
(SUPPLIER_ID, SUPPLIER_NAME, CONTACT_NAME, DOB)
VALUES(6, 'rr', 'ss','2019-06-19');

Error:

SQL Error [1843] [22008]: ORA-01843: not a valid month VALUES(6, 'rr', 'ss','2019-06-19'), Error Msg = ORA-01843: not a valid month ORA-01843: not a valid month

Upvotes: 3

Views: 2394

Answers (3)

Rajit Satija
Rajit Satija

Reputation: 11

  • I am sure you would have used DOB as date when defining the table.
  • You may directly enter date in DD/MON/YYYY format, with MON as first three letters of month.
  • To format date to YYYY-MM-DD format, you may use DATE before the string which will look like DATE '2019-07-20'

Here's a quick run through for all the points


create table supplier(SUPPLIER_ID number, SUPPLIER_NAME varchar2(20), CONTACT_NAME varchar2(20), DOB date);

insert into supplier values(12, 'supplier1', 'contact1', '18/JAN/2019');
insert into supplier values(13, 'supplier2', 'contact2', date '2019-07-20');

select * from supplier;

SUPPLIER_ID SUPPLIER_NAME        CONTACT_NAME         DOB
----------- -------------------- -------------------- ---------
         12 supplier1            contact1             18-JAN-19
         13 supplier2            contact2             20-JUL-19

Hope it sorts!

Upvotes: 1

Andrews B Anthony
Andrews B Anthony

Reputation: 1381

You need to covert your date string to DATE type. Before inserting the value

INSERT INTO TEST.SUPPLIER (SUPPLIER_ID, SUPPLIER_NAME, CONTACT_NAME, DOB) 
VALUES(6, 'rr', 'ss',TO_DATE('2003/05/03', 'yyyy/mm/dd'))

Upvotes: 0

Shabbir Ali
Shabbir Ali

Reputation: 174

Please try the below query:

INSERT INTO TEST.SUPPLIER (SUPPLIER_ID, SUPPLIER_NAME, CONTACT_NAME, DOB) VALUES(6, 'rr', 'ss',DATE '2019-06-19');**

DATE keyword interprets the following string as a date.

Upvotes: 6

Related Questions