4 Replies Latest reply on Sep 14, 2018 8:11 AM by Sherzodbek Ibragimov

    Tableau showing "Null" when date value exists in Oracle database

    Scott Heidenreich

      Hello,

       

      I created a data extract from an Oracle database and was seeing a date when using the Java application that uses the Oracle database.  However, in the Tableau data extract occasionally a date value showed as null when it existed in the Oracle database.  I come to find that the reason is that the date entered into the Oracle database shows on the screen as 5/5/15 but is stored as the Oracle equivalent of 5/5/0015 due to a data entry error.

       

      I'm getting the data corrected and better data validation built into the Java application - however, the process of tracking down this data error was made much more painful because Tableau showed the date as null instead of what it actually was - a date in the year 15 or 16 (as in the first century).  If Tableau had shown the date properly, then it would have been quickly apparent in a visualization what the problem was.

       

      If I convert the date field to a number in the Tableau extract - a value shows up for the year 15 and 16 dates, but If I change that column to a date it turns null.

       

      Is there any way to get Tableau to handle really old dates to prevent this type of issue and make it easier for cleaning up data as it is being visualized?

       

      Example:

       

      Oracle Date value: 05/06/0015, numeric value: -68359

      Tableau Date value: null, numeric value: -68359

       

      None of the date functions will act on these first century dates, but interestingly, the STR() function correctly translates the "null" value to "May 6 15 0:00:00"

       

      Thanks,

       

      Scott