informaticainformatica-cloudinformatica-powerexchange

Cannot convert type string to date

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.

Solution

  • 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.