CODEWITHSUNDEEP
sqloracle-database
Krunal Akbari
Krunal Akbari

Reputation: 374

ORA-00984: column not allowed here in oracle sql

insert into sales_order
( orderno , clientno , orderdate , delyaddr , salesmanno , delytype , billyn , delydate , orderstatus) 
values 
(
    'O19001',
    'C00001',
    12-jun-2014,
    'Nadiad',
    'S00001',   
    'F',
    'N',
    20-jul-2014,
    'In Process'
);


desc sales_order;
Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDERNO                                            VARCHAR2(6)
 CLIENTNO                                           VARCHAR2(6)
 ORDERDATE                                          DATE
 DELYADDR                                           VARCHAR2(25)
 SALESMANNO                                         VARCHAR2(6)
 DELYTYPE                                           CHAR(1)
 BILLYN                                             CHAR(1)
 DELYDATE                                           DATE
 ORDERSTATUS                                        VARCHAR2(10)

Upvotes: 0

Views: 932

Answers (3)

Viktor Török
Viktor Török

Reputation: 1319

Instead of the 12-jun-2014 string literal, you can use the following function in your SQL statement:

to_date('2014.07.12', 'yyyy.mm.dd')

Upvotes: 1

Popeye
Popeye

Reputation: 35900

Always remember that dates are not string and not any specially treated data type. It is just they need to be input as string and convert to date.

You have three options to convert string to date as follows:

  1. Use date literal - date '2020-12-31' (ISO standard format - yyyy-mm-dd)
  2. Use to_char - to_char('2020-12-31','yyyy-mm-dd') - given date and applied format must match.
  3. Use the string format as per NLS_DATE_FORMAT and oracle will do implicit conversion. If NLS_DATE_FORMAT is 'dd-mon-yyyy' then you can use string '31-dec-2020' to insert it into some date column.

Upvotes: 2

The Impaler
The Impaler

Reputation: 48770

You can use ISO format (YYYY-MM-DD) for the date literals. For example:

insert into sales_order (
  orderno , clientno , orderdate , delyaddr , 
  salesmanno , delytype , billyn , delydate , orderstatus) 
values (
    'O19001',
    'C00001',
    date '2014-06-12',
    'Nadiad',
    'S00001',   
    'F',
    'N',
    date '2014-07-20',
    'In Process'
);

Upvotes: 3

Related Questions