1 Reply Latest reply on Jan 14, 2016 11:15 AM by Yuriy Fal

    Date function error

    Joe Li

      Scenario 1:

      • Connect to an Excel file
      • Add an calculation item in Tableau: DATE("4/15/2015")
      • Drop the new item into your report
      • Seems no problem

       

      Scenario 2:

      • Connect to a live Database (Oracle)
      • Add an calculation item in Tableau: DATE("4/15/2015")
      • Drop the new item into your report
      • You may encounter an error:
      • dateError.jpg
      • Show Query:

      SELECT TO_NUMBER(TO_CHAR(TRUNC(CAST(N'4/15/2015' AS DATE)),'DD'))

      FROM YOURTABLENAME

       

      • Above SQL line do not work in a PL/SQL developer
      • If I change DATE("4/15/2015") to DATE("15-APR -15") in Tableau, everything works fine.

       

      Is this a bug? of Tableau or Oracle?

       

          

        • 1. Re: Date function error
          Yuriy Fal

          Hi Joe,

           

          Show Details information could help to reveal an ORA- error --

          guess it could be something like ORA-01843.

           

          Well, it's a known behavior of Oracle DBMS --

          implicit CAST'ing a STRING (like "4/15/2015") to a DATE

          could result to a valid date -- when using "mm/dd/yyyy" mask --

          or give an error -- when using "dd/mm/yyyy" mask.

          Which one is used depends on your Oracle environment.

           

          Using DATE("2015-04-15") is a safe way of doing in Tableau.

          Most DBMS engines cast this string correctly as "yyyy-mm-dd".

           

          Hope this could help.

           

          Yours,

          Yuri