2 Replies Latest reply on Aug 10, 2016 2:38 PM by Khang Pho

    YYYYMMDDHHMMSS date format v_9.3

    Rob Harrison

      I have a date field, Device Time we will call it, that brings in dates in the yearmonthdatehourminutesecond format (YYYYMMDDHHMMSS). It is all mashed together without delimiters.

       

      For example: 20160802141701

       

      In version 9.3, how can I convert this date into a readable format for Tableau? I need to be able to slice and dice by Year, Month, Day, and Hour. This currently shows up as a string on the dimensions shelf.

       

      I have used calculations in the past to split up YYYYMMDD, but nothing quite this detailed. The data in this dashboard will be refreshed on a daily level, as well as be used by other sheets/ dashboards, so a scalable solution is most appreciated.

       

        • 1. Re: YYYYMMDDHHMMSS date format v_9.3
          Benjamin Greene

          One idea would be to create new calculated fields for Year, Month, Day, Hour, Minute and Second as follows:

           

          Year=INT(LEFT(STR([Device Time]), 4))

          Month=INT(LEFT(RIGHT(STR([Device Time]), 10), 2))

          Day=INT(LEFT(RIGHT(STR([Device Time]), 8), 2))

          Hour=INT(LEFT(RIGHT(STR([Device Time]), 6), 2))

          Minute=INT(LEFT(RIGHT(STR([Device Time]), 4), 2))

          Second=INT(RIGHT(STR([Device Time]), 2))

           

          And then make a new field with the following syntax:

           

          Datetime=MAKEDATETIME(MAKEDATE([Year], [Month], [Day]), MAKETIME([Hour], [Minute], [Second]))

           

          EDIT:

           

          Just realized MAKEDATETIME, MAKEDATE and MAKETIME aren't available for all data sources. So another idea (if the above doesn't work) would be to still make those six fields the exact same way, just leave off the INT part at the beginning, so they are all formatted as strings. Then you could try making a field with this syntax:

           

          Datetime=DATETIME([YEAR]+"-"+[MONTH]+"-"+[DAY]+" "+[HOUR]+":"+[MINUTE]+":"+[SECOND])

          • 2. Re: YYYYMMDDHHMMSS date format v_9.3
            Khang Pho

            Try DATEPARSE("yyyyMMddhhmmss",[Device Time])

            1 of 1 people found this helpful