4 Replies Latest reply on Jan 11, 2011 5:34 AM by . GregSa

    String to date conversion returns null values

    . GregSa

      Hello,

       

      Trying to convert a field from string to date using a calculated field, and its returning all null values.  The datasource is a mysql database.  I have built multiple worksheets and dashboards with this datasource successfully.

       

      Example of date format "01/07/2010 10:30A"

       

      I have tried using DATE() and DATE(LEFT(),10)) functions, but both functions return all null values.

       

      However, If I use a data extract and create a calculated filed using either of the functions above it returns a good date value.

       

      Ideas

        • 1. Re: String to date conversion returns null values
          Joe Mako

          It could be something specific to your setup, as I could not reproduce the situation with your example. Both

          DATE([Field Name])
          and
          DATETIME([Field Name])
          conversion functions worked for me. Have you sent an email to support@tableausoftware.com with your log files yet?

          • 2. Re: String to date conversion returns null values
            Dean Glasener

            Joe, could you share in more detail about log files ( Server, Desktop, Digital, Reader ) and how best to use for problem solving?

             

            I've found View - Describe Sheet - to be helpful - and even more data is available when    save to clipboard.

            Where are the  config files - - - - are they  XML?      is there a good practice for what settings in the file?

             

            Joe - your forum posts have been an aid to my increasing slowly my knowledge of Tableau( and all Tableau videos & traing)

             

            dean

            • 3. Re: String to date conversion returns null values
              Joe Mako

              Dean, thank you, I am glad I have been able to help.

               

              As for log files see http://www.tableausoftware.com/support/knowledge-base/sending-tableau-desktop-log-files and http://www.tableausoftware.com/support/knowledge-base/creating-tableau-server-log-files for how to find them. As for what you can do with them, there is no documentation, the log files were made readable primarily for Tableau Support personnel. You might be able to see some things by watching the log files as you do things in Tableau, but the most common use for them is to see the SQL Tableau is generating.

               

              The Describe sheet is a nice description of the worksheet, but it is read only and you have to click to get the data. The .tbw files are just XML, and you can edit them at your own risk, Tableau will not supported edited XML, and there is not much talk about it on the forum. I have edited the XML with success, and I know other have too, along with auditing Tableau workbooks by inspecting the XML. Packaged workbooks, the .twbx files, are just .zip files for the .twb XML, the data source, and any other supporting files.

               

              There is an XML file for preferences, see Andy's comment at The specified item was not found.

              • 4. Re: String to date conversion returns null values
                . GregSa

                Received solution from Tableau Support:

                 

                The MySQL connection needs the date to be in ISO format (YYYY-MM-DD) in order for ISDATE and DATE to function. If your calculated field re-orders the string into this format, the DATE function should resolve successfully.

                 

                Works, fine.

                1 of 1 people found this helpful