CODEWITHSUNDEEP
oracle-database
HEEN
HEEN

Reputation: 4721

Not a valid month while inserting data in oracle

I am inserting data into the table with the below query..

INSERT INTO xxcus.xxacl_pn_agrmnt_mst
        (mkey, transaction_type, survey_area_7_12, ref_date, status,
         mst_date, remarks, tran_type, created_by, creation_date,
         last_update_date, last_updated_by, delete_flag
        )
 VALUES (1, 'AGR', 'khan,', '29-09-2016', 'AGD',
         '11/09/2016', 'Test', 'AM', '5681', '29-09-2016 17:10:19',
         '29-09-2016 17:10:19', '5681', 'N'
        )

But getting error as

Not a valid month for 29-09-2016

Here is the code from where I am inserting

xw.WriteElementString("REF_DATE", txtRefdate.Value);

I don't know what is wrong here

Upvotes: 0

Views: 899

Answers (2)

MT0
MT0

Reputation: 167972

'29-09-2016 17:10:19' is not a date it is a string.

Oracle will use the NLS_DATE_FORMAT session parameter as the format mask when implicitly converting a string to a date (i.e. when you try to insert a string value into a date column) and if this format mask does not match the format of the string then you will get an error.

To generate a date you should explicitly convert the string to a date either by:

Your query should be (if you use ANSI literals):

INSERT INTO xxcus.xxacl_pn_agrmnt_mst (
  mkey,
  transaction_type,
  survey_area_7_12,
  ref_date,
  status,
  mst_date,
  remarks,
  tran_type,
  created_by,
  creation_date,
  last_update_date,
  last_updated_by,
  delete_flag
) VALUES (
  1,
  'AGR',
  'khan,',
  DATE '2016-09-29',
  'AGD',
  DATE '2016-09-11',
  'Test',
  'AM',
  '5681',
  TIMESTAMP '2016-09-29 17:10:19',
  TIMESTAMP '2016-09-29 17:10:19',
  '5681',
  'N'
)

Upvotes: 1

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

you should cast datatype of your date columns by

to_date('29-09-2016 17:10:19', 'DD-MM-YYYY HH24:MI:SS')

Upvotes: 2

Related Questions