11 Replies Latest reply on Sep 29, 2017 5:48 PM by Allen Kei

    Converting string to Date time format

    Monika Singhal

      Hi,

       

      I am looking to convert string like 1160510204551000 to datetime format when working in Tableau. Can you let me know how this can be done? Thanks in advance,

       

      year is 2 and third digit

      month is represented by 4th and fifth digit

      date is  6 and seventh digit

      hour is 8th and ninth digit

      Mins is 10 and 11th digit

      Secs is 12 and 13th digit

        • 1. Re: Converting string to Date time format
          Andrew Watson

          Date should be straightforward with MAKEDATE. Try:

           

          MAKEDATE('20' + MID([Field],2,2),MID([Field],4,2),MID([Field],6,2))

           

          Time is similar with MAKETIME:

           

          MAKETIME(MID([Field],8,2),MID([Field],10,2),MID([Field],12,2))

           

          However combiing them is slightly more complex. The time needs to be converted to seconds.

           

          You can extract out the hours, minutes and seconds separately to make reading easier. In this example I'll put them in one formula, which should give you the number of seconds:

           

          INT(MID([Field],8,2))/3600 + INT(MID([Field],10,2))/60 + INT(MID([Field],12,2))

           

          Then you can do  DATEADD to get the full date time: DATEADD('second',[SecondsField],[MakedateField]) or (more difficult to read):

           

          DATEADD('second',INT(MID([Field],8,2))/3600 + INT(MID([Field],10,2))/60 + INT(MID([Field],12,2)),MAKEDATE('20' + MID([Field],2,2),MID([Field],4,2),MID([Field],6,2)))

           

          I strongly recommend you make these formulas separately as I haven't tested and there could be problems with the brackets and syntax.

          • 2. Re: Converting string to Date time format
            Shinichiro Murakami

            Here you go.

             

             

             

            [Conversion]

            dateparse("yyyy/MM/dd","20"+mid([Data],2,2)+"/"+mid([Data],4,2)+"/"+mid([Data],6,2))

            +float(mid([Data],8,2))/24

            +float(mid([Data],10,2))/(24*60)

            +float(mid([Data],12,2))/(24*60*60)

             

             

             

            Thanks,

            Shin

            • 3. Re: Converting string to Date time format
              kettan

              FIRST DIGIT CONSTANT

               

              If prefix is a constant (1), this might also be a way:

               

              DATEPARSE( "1yyMMddhhmmss", [DateStr] )

               

               

              In case 1 was a formatting code like y, s, M, etc, you could enclose it with singles quotes.

              Of course, you could do this anyway 

               

              DATEPARSE( "'1'yyMMddhhmmss", [DateStr] )

               

               

              IGNORE TEXT

               

              I don't know if DATEPARSE has any formatting code for ignoring string positions with varying content, but interestingly, it is possible to use other codes for this purpose, such as:

               

              day of week in month - 2 (2nd Wed in July)

              W  week of month - 2

               

              The examples below use F where text should be ignored.

               

               

              FIRST DIGIT VARIES

               

              Even if the first digit isn't a constant, this seems to work too:

               

              DATEPARSE( "FyyMMddhhmmss", [DateStr] )

               

               

              TIME

               

              This formula might work for TIME only:

               

              DATEPARSE( "FFFFFFFhhmmss", [DateStr] )

               

              • 4. Re: Converting string to Date time format
                kettan

                Be aware that with two digits for year, DATEPARSE chooses a suitable century itself.

                Currently, the break is at 30/31. I don't know when this will be changed to something else.

                 

                If year is 30, it will be 2030.

                If year is 31, it will be 1931.

                 

                • 5. Re: Converting string to Date time format
                  kettan

                  ARE F and W safe to use?

                   

                  I haven't been able to make them do anything, and thus currently of the opinion that they are safe to use as code for text to ignore. But of course I cannot and will not give any guarantees, so whoever uses them uses them at their own risk 

                   

                  Why it is like this, I don't know. It could be because it isn't currently supported as noted  here.

                  If this is the case, errors might arise if F and W suddenly are supported in the future.

                   

                  TESTS

                   

                  All these calculations return the same result ( 2016-02-01 ):

                   

                  DATEPARSE( "yyyy-MM-F", "2016-02-1" )

                  DATEPARSE( "yyyy-MM-F", "2016-02-2" )

                  DATEPARSE( "yyyy-MM-F", "2016-02-3" )

                  DATEPARSE( "yyyy-MM-F", "2016-02-4" )

                   

                  DATEPARSE( "yyyy-MM-W", "2016-02-1" )

                  DATEPARSE( "yyyy-MM-W", "2016-02-2" )

                  DATEPARSE( "yyyy-MM-W", "2016-02-3" )

                  DATEPARSE( "yyyy-MM-W", "2016-02-4" )

                   

                   

                  F  day of week in month - 2 (2nd Wed in July)

                  W  week of month - 2

                  Formatting Dates and Times - ICU User Guide

                  • 6. Re: Converting string to Date time format
                    Monika Singhal

                    Thanks all for the response. I am making live connection to DB2 database in my workbook. Dateparse function is not showing up with Live connection to db2. I am getting below error.

                     

                    Unknown function Dateparse called.  Can you let me know if I can use some other function to convert string to Datetime?

                    • 7. Re: Converting string to Date time format
                      kettan

                      I am making live connection to DB2 database in my workbook. Dateparse function is not showing up with Live connection to db2. I am getting below error.

                      DATEPARSE isn't available for DB2 as documented here:

                      This function is available for non-legacy Microsoft Excel and text file connections, MySQL, Oracle, PostgreSQL, and Tableau data extract data sources. Some formats may not be available for all data sources.  Onlinehelp > Date Functions

                      • 8. Re: Converting string to Date time format
                        kettan

                        Can you let me know if I can use some other function to convert string to Datetime?

                        Could you check if this works:

                         

                        RAWSQL_DATETIME("TIMESTAMP_FORMAT( SUBSTR( %1, 2, 12 ), 'YYMMDDHH24MISS' )", FIELDNAME )

                         

                        If it doesn't and you have a tool to query DB2, could you check if this works:

                         

                        TIMESTAMP_FORMAT( SUBSTR( FIELDNAME, 2, 12 ), 'YYMMDDHH24MISS' )

                         

                        You could use RR instead of YY.

                         

                        Ps.  I have never used DB2 and therefore base my knowledge on this:

                         

                        IBM Knowledge Center : TIMESTAMP_FORMAT

                        IBM Knowledge Center : SUBSTR

                         

                        db2  convert_string_to_date  rawsql 

                        • 9. Re: Converting string to Date time format
                          Monika Singhal

                          I created  a new calculated column with below formula and this worked with Live connection to DB2 to convert string to datetime.

                           

                          DATETIME(STR(20)+MID([Timestamp],2,2)+"-"+MID([Timestamp],4,2)+"-"+MID([Timestamp],6,2)+"-"+MID([Timestamp],8,2)+"."+MID([Timestamp],10,2)+"."+MID([Timestamp],12,2))

                          • 10. Re: Converting string to Date time format
                            kettan

                            Could you check if this calculation also works in Tableau?

                             

                            RAWSQL_DATETIME( "TIMESTAMP_FORMAT( SUBSTR( %1, 2, 12 ), 'YYMMDDHH24MISS' )", [Timestamp] )