Avatar of kbit
kbit

asked on 

"ora-01840: input value not long enough for date format" error on some data

My sql is below.  The script runs fine for a few hundred records and then it fails with the error message "ora-01840: input value not long enough for date format" .  How can I get around the apparant "bad" data that it is encountering before the failure.


select to_char(to_date(a.jvdate,'YYYYMMDD'),'DDMONYYYY')||'#'||a.period||'#'||substr(a.glcode,1,6)||'#'||substr(a.glcode,11,5)||'#'||b.trx_ref_1||'#'||'Summary'||'#'||a.descr||'#'||a.jvline||'#'||( select zcode from cora_lookup d where to_char(substr(a.glcode,7,4)) = to_char(d.opcode) )||'#'||a.descr||'#'||a.jvtype||'#'||a.entities_0||'#'||a.jvref||a.jvline||a.jvtype||'#'||b.trx_ref_0||'#'||to_char(to_date(a.mf_date,'YYYYMMDD'),'DDMONYYYY')||'#'||substr(a.glcode,7,4) from nmltrx a, nmltrx1 b, cora_lookup d
where a.jvref=b.jvref and a.source=b.source and a.period=b.period and a.jvtype=b.jvtype and
 substr(a.period,1,4) > '2007' and
 substr(a.glcode,7,4) = d.opcode and
 substr(a.period,5,2) != '00' and
 substr(a.glcode,1,2)='32' and
 substr(a.jvtype,1,2) = 'PA' and
 a.entities_0 <> 0 and
 substr(a.glcode,3,4) in
( '4558' , '1126' , '4549' , '3531' , '3532' , '4238' , '0454', '1151' , '4250' , '4261' , '3533' , '4630' , '1379' , '3934' , '1168' , '3648' , '3649' , '3914' , '2582' )
Oracle Database

Avatar of undefined
Last Comment
kbit

8/22/2022 - Mon