oracle导入日期格式,oracle导入文件时,日期格式问题

太过爱你忘了你带给我的痛 2023-01-18 04:29 220阅读 0赞

oracle导入文件时,日期格式问题

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)

用这个模板导入数据时

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

会出现

Record 9: Rejected - Error on table ZHICAI\_RECORD\_TMP, column CREATE\_DATE.

ORA-01861: literal does not match format string

这种错误,主要是因为日期格式不匹配,这时要对导入的数据进行格式化处理

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)

用这个模板导入数据时,便不会出现以上报错

还没有评论，来说两句吧...