1

I have a flat file with date coming in as 2023062 which I am using the conversion substr(LTRIM(RTRIM(LINE)),76,8) to get it as a variable named INVC_DT, and i created output column as o_INVC_DT with the expression TO_DATE(INVC_DT,'YYYYMMDD'). When I run the workflow, it gives me the error messages

"Transformation Evaluation Error [<> [TO_DATE]: invalid string for converting to Date ... t:TO_DATE(s:'',s:'YYYYMMDD')]" and "Transformation [REC_TRAN] had an error evaluating output column [o_INVC_DT]. Error message is [<> [TO_DATE]: invalid string for converting to Date ... t:TO_DATE(s:'',s:'YYYYMMDD')].".

I have tried to change the output expression to TO_DATE(INVC_DT,'MMYYYYDD'), but it still did not work.

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
Bond
  • 25
  • 2
  • 13
  • Your input string only has 7 characters (2023062) while your format string has 8 characters (YYYYMMDD) – NickW Jul 18 '23 at 06:24
  • Sorry, it is 20230624. It is worth mentioning that the target table takes 10 characters. When I do debug I see the date field in the target table containing zeros for the time as you would see in timestamp right to seconds – Bond Jul 18 '23 at 08:14
  • So presumably you have data somewhere in your file that is not a valid date in the format YYYYMMDD. You need to find that data and fix it. If you turn the Infa logging level up to verbose it should show the data and make it easier to find – NickW Jul 18 '23 at 08:50

1 Answers1

0

Why dont you check first and then convert?

IS_DATE() - this will check if your data is a date in the mentioned format or not.

iif(IS_DATE(INVC_DT,'YYYYMMDD'),TO_DATE(INVC_DT,'YYYYMMDD'),null)

This will ensure there will be no failure.

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33