5 Replies Latest reply on May 12, 2009 4:39 PM by James Baker

    Converting String to Date

    . jdugge1

      Hello all,

       

      I have a string field in the following format: YYYYMMDD. eg: 20010425. I would like to do some time series analysis with this field but am unable to change the type of this field to a date in Tableau. I tried to use the DATE function on this field but am getting error. Any suggestions on how to do this would be really appreciated.

       

      Thanks..

        • 1. Re: Converting String to Date
          James Baker

          Tableau doesn't know how to parse an 8-digit string as a date uniquely.  Here's US format with separators:

           

          DATE(MID([yyyymmdd],5,2)+"/"+MID([yyyymmdd],7,2)+"/"+MID([yyyymmdd],1,4))

          • 2. Re: Converting String to Date
            guest contributor

            I calculated a new field using this approach, but when I used the resulting date variable I got a message "Oracle database error 1843: ORA-01843: not a valid month. Any idea what gives here?

            • 3. Re: Converting String to Date
              James Baker

              Huh.  Hearing just that error message, I would say there's a day in a month slot somewhere (number >12).  I would check the data again, YMD v. something else, and check that the parsing numbers match your string precisely (are 1, 5, and 7 the right locations?).

              • 4. Re: Converting String to Date
                . imaging

                Hi,

                I also have that problem with date strings. As tableau is a software that is in worldwide use, I would suggest a new function that tacles the problem a little bit more user-friendly. I many programming languages there is a function like parseDatetime(datestring, formatstring). E.g. parseDatetime("2009.05.09 18:00", "YYYY.MM.DD HH:mm"). This would make things much easier. I would appreciate it if the next update had such a function.

                Thanks, in advance!

                • 5. Re: Converting String to Date
                  James Baker

                  The DATE() and DATETIME() functions do take strings as input.  DATETIME("2009.05.09 18:00") is parsed as you desire, for example.  Passing in an additional strftime-type format string would still be a useful addition, though - that would be the only way to deal with (as above) an otherwise undifferentiated string of eight digits like "20032005" which could be "YYYYDDMM" or "DDMMYYYY".  I'll file this for development.