1 Reply Latest reply on Aug 19, 2012 5:54 PM by Dimitri.B

    Converting Year(string) to Date format in Oracle

    fahim m
      Hi,
      I used the calculated field (DATE('01-01-' + STR([YearField]))) to convert a year from a string to date. It worked when data was in excel. Now when the data was migrated to Oracle - it gives a database error .
      Oracle database error 1843: ORA-01843: not a valid month
      Please, any help is appreciated
      Thanks
      Fahim.
        • 1. Re: Converting Year(string) to Date format in Oracle
          Dimitri.B

          Try this instead:

           

          RAWSQL_DATE("TO_DATE(%1, 'DD-MM-YYYY')", '01-01' + STR([YearField]))

           

          The problem with your formula and Oracle is probably the way Oracle interprets date's components. I think Tableau tries to "outsource" your custom date formula to Oracle, which confuses day with month number, because this is how Oracle was configured by administrator.

          In any case, it is safer to use RAWSQL which will allow you to use Oracle native functions with no confusion.