ORA-01843: NOT A VALID MONTH

User_IRLXR

Hi everyone,

I'm currently developing an ETL. In my source DB I have the following field F_PET_DATOS_ULTIMA which is a VARCHAR type.

In my target table this field needs to be a DATE type. For this reason, I've done the following:

SELECT NVL(TO_DATE(SUBSTR(F_PET_DATOS_ULTIMA,1,10),'DD/MM/YYYY'),'01/01/1500') FROM TABLE

The picture below, shows the result of running the query above. It works. However, when I put it into my ETL, ODI displays the error ORA-01843: Not a valid month. Any idea?

Gianni Ceresa

Hi,

From a logical point of view, your expression is wrong.

You are trying to turn a string into a date, and deal with the NULL cases. But you have the wrong order in your expression: if the source field is NULL you are returning a string, and not a date.

Try something like this:

TO_DATE(SUBSTR(NVL(F_PET_DATOS_ULTIMA,'01/01/1500'),1,10),'DD/MM/YYYY')

Apply the NVL directly on the source field, if you have a NULL pass your hardcoded string instead. From there apply the rest of the logic by first taking only first 10 chars and then turning the string into a date.

User_IRLXR

it works! Thanks!