Forum Stats

  • 3,827,608 Users
  • 2,260,798 Discussions
  • 7,897,314 Comments

Discussions

ORA-01843: NOT A VALID MONTH

User_IRLXR
User_IRLXR Member Posts: 2 Green Ribbon

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?


Answers

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,570 Blue Diamond
    edited Apr 5, 2022 2:44PM

    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
  • User_IRLXR
    User_IRLXR Member Posts: 2 Green Ribbon

    it works! Thanks!