I am aware that this issue has been discussed before , but since i am facing the issue even after handling blank or null values.
I am reading a file that has been sent to us from the source system and i am reading the date fields as character of length 8.
in the transformer before loading into the Oracle table where the filed sits a date field , i am doing a convert function where i have handled both Null blank and zero values before i apply the convertion function .
below is the derivation i am using:
Code: Select all
If ((Trim(Lnk2XFM.FLD1) = '') Or (IsNull(Lnk2XFM.FLD1)) or (Trim(Lnk2XFM.FLD1) = '00000000')) Then SetNull() Else StringToDate(Trim(Lnk2XFM.FLD1),"%yyyy%mm%dd")
XFM,3: Conversion error calling conversion routine date_from_string data may have been lost.
I tried debugging cause the file had few more date fields , i kept adding one filed after the other and figured that this one particular date field is causing and issue.
the values that i am seeing for this field are as follows
either nulls or 00000000 or a valid date in the format YYYYMMDD.
Any help is greatly apprictaed in identifying the issue as i seem to be lost .
Thanks in advance