0

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

MT0
  • 143,790
  • 11
  • 59
  • 117
Nad
  • 4,605
  • 11
  • 71
  • 160

2 Answers2

2

you should cast datatype of your date columns by

to_date('29-09-2016 17:10:19', 'DD-MM-YYYY HH24:MI:SS')
Frank Ockenfuss
  • 2,023
  • 11
  • 26
  • let me know how to add it here `GridPayInfo.Rows[intGrdPay].Cells[GridPayInfo.Columns.GetColumnIndexByDataField("CHEQUE_DT")].Text.Trim()` – Nad Sep 29 '16 at 12:26
  • could you please add the complete code of your `xw` object mentioned above? – Frank Ockenfuss Sep 29 '16 at 12:59
1

'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'
)
MT0
  • 143,790
  • 11
  • 59
  • 117
  • so how should I add it here `GridPayInfo.Rows[intGrdPay].Cells[GridPayInfo.Columns.GetColumnIndexByDataField("CHEQUE_DT")].Text.Trim()`? – Nad Sep 29 '16 at 12:26
  • @nad That is not SQL and I am unable to answer based on a tiny snippet of code without any context. You would do better asking a question with **all** the relevant code. Make a [MCVE]. – MT0 Sep 29 '16 at 12:37