5 Replies Latest reply on Sep 25, 2018 7:06 PM by swaroop.gantela

    Working with strings and datetime in Tableau 9.3

    Migue Rivas

      Hello everyone,

       

      I'm having a problem using Tableau ODBC connection to DB2 database (AS400 on the other side).

       

      I need to do some calculations with an initial date and a final date so I can get the difference in a datetime datatype.

       

      My date string is like this:

       

      '20180913'

       

      By the other hand in a completely different field, I have my time string like this:

       

      '12:35:00' (8 long characters both fields)

       

      In tableau 9.3 there is no DATEPARSE, so how can I convert and make a relation between my date and time so I can get the difference between them?

       

      Thanks in advance for any suggestion.

        • 1. Re: Working with strings and datetime in Tableau 9.3
          swaroop.gantela

          Migue,

           

          There are very likely better ways to do this, but maybe this can give an idea.

           

          Basically, you can use DATEADD to build up what you want.

           

          I had two fields in Excel: Date (20180913) and Time (12:35:00)

          Maybe because I was using Excel, Tableau pulled the Time in as 12/30/1899 12:35:00.

           

          Whatever date value that gets pulled in for your Time field, you can use that as a base.

          This was the calculated field that I used:

          DATEADD ( 'day', ( INT ( RIGHT ( [Date], 2 ) ) - 30 ),

               DATEADD ( 'month', INT ( MID ( [Date], 5, 2 ) ),

                    DATEADD ( 'year', ( INT ( LEFT ( [Date], 4 ) ) - 1900 ), [Time] ) ) )

           

          Please see workbook v10.0 attached in the Forum Thread.

          281895dt.png

          1 of 1 people found this helpful
          • 2. Re: Working with strings and datetime in Tableau 9.3
            Migue Rivas

            Hello SWAROOP.GANTELA, thank you so much for the help you have provided me.

             

            I understand the logic, but I'm a little bit confused because my two fields are different than yours.

             

            I see your fields are like this:

             

            Date = '20180913'

             

            Time = '30/12/1899 12:35:00'

             

             

            But in my case the fields are not related between the date and time field.

             

            Date = '20180914'

             

            Time = '12:30:00'

             

            And is all in a string datatype.

             

            When I try the solution you provided in the last post, Tablaeu gives me an error expecting another format or datatype maybe?

             

            I tried to concatenate [Date] +" "+ [Time] but it does not work.

             

            I have attached an example so you can know what I'm talking about.

             

            Have a great day.

             

            PD: Just for the records, the odbc error I meant before, when I try to convert Time (in string) as a "Date and time" datatype, it gives an error saying the field is not valid.

            • 3. Re: Working with strings and datetime in Tableau 9.3
              swaroop.gantela

              Migue,

               

              Ah, I wasn't sure how it would come through on your side.

              Here is an extension of the same concept:

               

              DATEADD('second',INT(RIGHT([Time],2)),

              DATEADD('minute', INT(MID([Time],4,2)),

              DATEADD('hour', INT(LEFT([Time],2)),

              DATEADD('day', (INT(RIGHT([Date],2))-30),

              DATEADD('month', INT(MID([Date],5,2)),

              DATEADD('year',(INT(LEFT([Date],4))-1900),#1899-12-30#))))))

               

              Apologies, I don't immediately have access to 9.3

              2 of 2 people found this helpful
              • 4. Re: Working with strings and datetime in Tableau 9.3
                Migue Rivas

                Thank you for the time you've spent swaroop.gantela.

                 

                The thing is I believe this only works with data gathered by excel and so.

                 

                In my case, the iSeries DB2 database pops up an error with the odbc driver, I believe this is because on the other side is expecting to receive the same datatype when is live update and not extract.

                 

                Each time I use the calculated field with dateadd function it gives me an error with the ODBC driver.

                 

                Anyway, your answer is valid for other databases so I want to say thank you for your post.

                 

                Have a great weekend.

                • 5. Re: Working with strings and datetime in Tableau 9.3
                  swaroop.gantela

                  Migue,

                   

                  Was checking to see if you found resolution for your issue.

                   

                  I saw another thread with a different approach and wondered if it may help you:

                  Re: Converting a string to date (10.1)

                   

                  I also saw that my previous attempt may have been wrong.

                  Maybe something along the lines of this is better:

                  DATEADD('second',INT(RIGHT([Time],2)),

                  DATEADD('minute', INT(MID([Time],4,2)),

                  DATEADD('hour', INT(LEFT([Time],2)),

                  DATEADD('day', (INT(RIGHT([Date],2)),

                   

                  DATEADD('month', INT(MID([Date Sec],5,2)) - 1,  // note the -1 needed here

                  DATEADD('year',(INT(LEFT([Date],4))-2000),#1999-12-31#))))))

                   

                  This is from this thread:

                  Re: Convert datetime string value to date and time