0

I created a table with parameters:

GUIDE_NUM   NOT NULL CHAR(4)  
LAST_NAME            CHAR(15) 
FIRST_NAME           CHAR(15) 
ADDRESS              CHAR(25) 
CITY                 CHAR(25) 
STATE                CHAR(2)  
POSTAL_CODE          CHAR(5)  
PHONE_NUM            CHAR(12) 
HIRE_DATE            DATE

But when I try to insert values into the table for example:

INSERT INTO GUIDE VALUES('AM01' ,'Abrams', 'Miles', '54 Quest Ave.','Williamsburg','MA', '01096','617-555-6032','6-3-2012');

There seems to be a problem with the DATE syntax because Oracle keeps displaying:

ORA-01843: not a valid month

and I even tried inputing different date formats like '06-03-2012' and it keeps displaying the same error.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Anthony Alvarez
  • 1
  • 1
  • just use `date'2012-03-06'` instead(presumably month is march(03)) – Barbaros Özhan Apr 07 '22 at 20:36
  • 3
    Check the answer for this question: https://stackoverflow.com/questions/50163432/oracles-default-date-format It has all the info you need to know. – Koen Lostrie Apr 07 '22 at 20:38
  • `'06-03-2012'` may look like a date but it is a string literal. Use a date literal: `DATE '2012-03-06'` or a timestamp literal: `TIMESTAMP '2012-03-06 00:00:00'` or use `TO_DATE('06-03-2012', 'DD-MM-YYYY')` to convert the string literal to a date. – MT0 Apr 07 '22 at 21:09
  • 2
    As a side observation, all of your character columns are defined as CHAR(n). This is a fixed-length format. So if someone's first name is 'Joe', it will still be 15 characters long - "Joe" and right padded with 12 spaces. What a waste of storage! And when searching, 'Joe' != 'Joe '. Much better to use VARCHAR2(n), where 'n' is a reasonable estimate of the max length needed. – EdStevens Apr 07 '22 at 21:59
  • I suggest familiarising yourself with the [syntax page for literals](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Literals.html). And as @EdStevens said, [avoid `char`](https://stackoverflow.com/a/42165653/230471). – William Robertson Apr 07 '22 at 22:02

0 Answers0