Reputation: 4735
I have a Oracle query in which I am getting error as
ORA-01843: not a valid month
I tried using to_date
also but still it didn't worked for me
Below is the query
INSERT INTO xxcus.xxacl_land_purc_invoice_view
(mkey, ref_purchase_id, ref_sr_no, ref_purhcase_type, org_id,
project_id, taluka_id, village_id, survey_area_7_12, doc_no,
invoice_id, invoice_num, vendor_id, vendor_name, invoice_amt,
batch_id, batch_name, exp_id, exp_type, remarks, created_by,
creation_date, last_update_date, last_updated_by
)
VALUES (139, 18, 1, 'E', 6089,
49292, 1, 14, '85/47', '693',
NULL, NULL, 2653609, 'K.A.JOSEPH', 1000,
572727, 'ICICI', 900077, 'Land Cost', 'TEST', 5681,
'18-02-2017 16:02:10', '18-02-2017 16:02:10', '5681'
)
Upvotes: 0
Views: 2092
Reputation: 39537
Use to_date with proper format mask:
to_date('18-02-2017 16:02:10','dd-mm-yyyy hh24:mi:ss')
Try this:
INSERT INTO xxcus.xxacl_land_purc_invoice_view
(mkey, ref_purchase_id, ref_sr_no, ref_purhcase_type, org_id,
project_id, taluka_id, village_id, survey_area_7_12, doc_no,
invoice_id, invoice_num, vendor_id, vendor_name, invoice_amt,
batch_id, batch_name, exp_id, exp_type, remarks, created_by,
creation_date, last_update_date, last_updated_by
)
VALUES (139, 18, 1, 'E', 6089,
49292, 1, 14, '85/47', '693',
NULL, NULL, 2653609, 'K.A.JOSEPH', 1000,
572727, 'ICICI', 900077, 'Land Cost', 'TEST', 5681,
to_date('18-02-2017 16:02:10','dd-mm-yyyy hh24:mi:ss'),
to_date('18-02-2017 16:02:10','dd-mm-yyyy hh24:mi:ss'),
'5681'
)
As per OP's mention about inserting current datetime, it's better to use sysdate
:
INSERT INTO xxcus.xxacl_land_purc_invoice_view
(mkey, ref_purchase_id, ref_sr_no, ref_purhcase_type, org_id,
project_id, taluka_id, village_id, survey_area_7_12, doc_no,
invoice_id, invoice_num, vendor_id, vendor_name, invoice_amt,
batch_id, batch_name, exp_id, exp_type, remarks, created_by,
creation_date, last_update_date, last_updated_by
)
VALUES (139, 18, 1, 'E', 6089,
49292, 1, 14, '85/47', '693',
NULL, NULL, 2653609, 'K.A.JOSEPH', 1000,
572727, 'ICICI', 900077, 'Land Cost', 'TEST', 5681,
sysdate,
sysdate,
'5681'
)
Upvotes: 2