Error converting string to date

[sshettar]

Hi All,

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")

But what i am facing is few records are getting dropped which doesnt show up on the director but for one warning that it shows as below

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

[ray.wurlod]

"99999999" would take the Else clause of your If statement. You need to add an IsValid() or IsValidDate() test as well.

[sshettar]

Sorry i misread your response, so are you suggesting that i instead convert the blanks nulls and 0000000s to 999999999 and then do a isvalid to test?

[ray.wurlod]

No. There's not need to convert. The IsValid() test will intercept any invalid values.