Error "not a valid month" SQL

Navigation
0

Good afternoon;

I'm doing an exercise for my Database course, they passed us a script to create the tables and do the inserts of the elements (I use the Oracle Application Express). But when executing any insert I get the following error:

  

ORA-01843: not a valid month

The code of the tables and inserts is as follows: 

CREATE TABLE CURSOS
( Codigo NUMBER, 
  Nombre VARCHAR2(20), 
  Cod_Profe NUMBER, 
  Max_Alumn NUMBER, 
  Fecha_Inic DATE, 
  Fecha_Fin DATE, 
  Num_Horas NUMBER,
  CONSTRAINT Cod_Curso_PK PRIMARY KEY (Codigo),    
  CONSTRAINT Cod_Profe_FK
    FOREIGN KEY (Cod_Profe)
    REFERENCES PROFESORADO(Codigo)
);


INSERT INTO CURSOS (Codigo, Nombre, Max_Alumn, Fecha_Inic, Fecha_Fin, Num_Horas) VALUES 
(1, 'Curso 1', 30, '01/01/2011', '31/12/2011', 100);
    
asked by Ivan Vieites Lores 20.04.2017 в 19:41
source

2 answers

0

It is likely that the date format is "MM / DD / YYYY", do the test in the following way: 

INSERT INTO CURSOS (Codigo, Nombre, Max_Alumn, Fecha_Inic, Fecha_Fin, Num_Horas) VALUES 
(1, 'Curso 1', 30, TO_DATE('01/01/2011', 'MM/DD/YYYY'), TO_DATE('31/12/2011', 'MM/DD/YYYY'), 100);
    
answered by 20.04.2017 / 21:29
source
0

Instead of the string representing dates use TO_DATE ; both for insert , update , etc.

It's like below in your sentence that you placed 

INSERT INTO CURSOS (Codigo, Nombre, Max_Alumn, Fecha_Inic, Fecha_Fin, Num_Horas) VALUES 
(1, 'Curso 1', 30, TO_DATE('01/01/2011', 'DD/MM/YYYY'), TO_DATE('31/12/2011', 'DD/MM/YYYY'), 100);

Notice that each element is indicated in which position it is and the separator:

  • DD : Day.
  • MM : Month.
  • YYYY : Year.
  • / : For this case the separator of each element is / ; depending on your system you should indicate it

The error that comes up is because the format to be interpreted must be MM / DD / YYYY; therefore the first default element Oracle considers it the Month; this depends how it is configured; with TO_DATE "we avoid" knowing this format; and if you change the environment you would not worry because the function will make the change

    
answered by 20.04.2017 в 19:48
Problem sending Plesk emails Concatenate bower components with gulp