oracle导入文件时,日期格式问题-创新互联
oracle导入文件时,日期格式问题
10年积累的成都做网站、成都网站建设经验,可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你,你也不认识我。但先做网站设计后付款的网站建设流程,更有东湖免费网站建设让你可以放心的选择与我们合作。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,'yyyymmddhh34miss')",
state_date "to_date(:create_date,'yyyymmddhh34miss')",
des_file_name)
用这个模板导入数据时,便不会出现以上报错
另外有需要云服务器可以了解下创新互联cdcxhl.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。
分享文章:oracle导入文件时,日期格式问题-创新互联
本文路径:http://pwwzsj.com/article/dgccdh.html