Date format issues when Oracle imports files

Date format issues when Oracle imports files

Load data

InFile '/iot/opthb/tibs_home/xcx/test/loadfile.txt '

Truncate into table zhicai_record_tmp

Fields terminated by '

Trailing Nullcols (src_file_name,create_date,state_date,des_file_name)

When importing data with this template

Loadfile.txt:

Jswlwyfcg_-_01111453.20170502_-_2357+0800.ctc.dat 20170502235916 20170503000027 T_IOT_DDR_20170502.025.144.055

Jswlwyfcg_-_01111454.20170502_-_2357+0800.ctc.dat 20170502235917 20170503000027 T_IOT_DDR_20170502.025.144.056

Jswlwyfcg_-_01111455.20170502_-_2357+0800.ctc.dat 20170502235917 20170503000042 T_IOT_DDR_20170502.025.144.057

Jswlwyfcg_-_01111456.20170502_-_2357+0800.ctc.dat 20170502235917 20170503000043 T_IOT_DDR_20170502.025.144.058

Jswlwyfcg_-_01111457.20170502_-_2358+0800.ctc.dat 20170502235918 20170503000058 T_IOT_DDR_20170502.025.144.059

Jswlwyfcg_-_01111458.20170502_-_2358+0800.ctc.dat 20170503000331 20170503000347 T_IOT_DDR_20170502.025.144.075

Jswlwyfcg_-_01111459.20170502_-_2358+0800.ctc.dat 20170503000332 20170503000347 T_IOT_DDR_20170502.025.144.076

Jswlwyfcg_-_01111460.20170502_-_2359+0800.ctc.dat 20170503000332 20170503000402 T_IOT_DDR_20170502.025.144.077

Jswlwyfcg_-_01111461.20170502_-_2359+0800.ctc.dat 20170503000333 20170503000402 T_IOT_DDR_20170502.025.144.078

Jswlwyfcg_-_01111462.20170502_-_2359+0800.ctc.dat 20170503000333 20170503000416 T_IOT_DDR_20170502.025.144.079

will appear

Record 9:rejected-error on table zhicai_record_tmp, column create_date.

Ora-01861:literal does not match format string

This error, mainly because the date format does not match, this time to the imported data to format processing

Load data

InFile '/iot/opthb/tibs_home/xcx/test/loadfile.txt '

Append into table zhicai_record_tmp

Fields terminated by '

Trailing Nullcols (Src_file_name,

Create_date "To_date (: create_date, ' Yyyymmddhh24miss ')",

State_date "To_date (: create_date, ' Yyyymmddhh24miss ')",

Des_file_name)

When using this template to import data, the above error will not appear

Date format issues when Oracle imports files