CODEWITHSUNDEEP
sqloracle-databaseora-00984
hamstrdethwagon
hamstrdethwagon

Reputation: 17

ORA-00984 Column not allowed here with date in oracle SQL

Im inserting values into this table 

    CREATE TABLE Flight ( 
    FlightNumber         char(7) primary key,
    ArrivalAirportCode   char(6)   references Airport  (Airport_code),
    DepartureAirportCode char(6)   references Airport  (Airport_code),
    AircraftNumber       varchar2(25) references Aircraft (AircraftNumber),
    ArrivalDate          date,
    ArrivalTime          Varchar2(5),
    DepartureDate        date,
    DepartureTime        varchar2(5)
    );

and here are the values Im inserting into it 

   INSERT INTO FLIGHT values
   ('CA3048',
   'LHR', 
   'EDI',
   'N859E',
   '14-NOV-2014',
   '22:15', 
   '14-NOV-2014', 
   '20:15');

And I get the column not allowed here error for the 2nd date I insert, but not the first one. I've tried putting quotes around the date but I just get another error.

Upvotes: 1

Views: 5303

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

'14-NOV-2014'

Why are you inserting a string in a DATE column? '14-NOV-2014' is a STRING and NOT a DATE. You should not depend on implicit data type conversion.

Always, convert the string into a DATE explicitly using TO_DATE and proper format mask.

For example,

TO_DATE('14-NOV-2014','DD-MON-YYYY')

One more thing,

DepartureTime varchar2(5)

Makes no sense. You already have a DATE column, a DATE would have the time element too.

No need of a separate time column. A DATE has both date and time elements stored in 7 bytes.

Oracle stores DATE in total of 7 bytes. Each byte in it stores values for an element of the DATE as follows:

Byte    Description
----    ------------------------------------------------
1       Century value but before storing it add 100 to it
2       Year and 100 is added to it before storing
3       Month
4       Day of the month
5       Hours but add 1 before storing it
6       Minutes but add 1 before storing it
7       Seconds but add 1 before storing it

All you need to do is just have 2 DATE columns:

CREATE TABLE Flight ( 
    FlightNumber         char(7) primary key,
    ArrivalAirportCode   char(6)   references Airport  (Airport_code),
    DepartureAirportCode char(6)   references Airport  (Airport_code),
    AircraftNumber       varchar2(25) references Aircraft (AircraftNumber),
    ArrivalDate          date,
    DepartureDate        date
);

And then insert the values as:

INSERT INTO FLIGHT values
   ('CA3048',
   'LHR', 
   'EDI',
   'N859E',
   TO_DATE('14-NOV-2014 22:15:00','DD-MON-YYYY HH24:MI:SS'),
   TO_DATE('14-NOV-2014 20:15:00','DD-MON-YYYY HH24:MI:SS')
);

Update

As mentioned in the comments by @GriffeyDog and @a_horse_with_no_name.

Alternatively, you could also the ANSI literal instead, for example:

timestamp '2014-11-14 22:15'

Upvotes: 7

Related Questions