5 Replies Latest reply on Dec 4, 2015 4:37 AM by Tableau kumar

    Converting text date field to numeric date field

    Abhishek Bhalotia

      Hi,

      I have a column in a text format which has date and time both
      for example

      Created

      31-12-2014 18:30

      I want this field to get converted into date field with time part removed

      How can i do it?

       

      Thanks,
      Abhishek

        • 1. Re: Converting text date field to numeric date field
          pooja.gandhi

          Abhishek,

           

          If you are connecting to any of these data sources:

           

          DATEPARSE function parameters:

          The DATEPARSE function is available for non-legacy Microsoft Excel and text file connections, MySQL, Oracle, PostgreSQL, and Tableau data extracts data sources. The DATEPARSE function takes two parameters, “format” and “string.”

           

          You can create a calculated field like: DATEPARSE('dd-MM-yyyy hh:mm', [Date String])

           

          The instead of using exact date, you can use the DAY datepart on the view to group other attributes by day without the time component.

           

          Capture.PNG

          • 2. Re: Converting text date field to numeric date field
            Abhishek Bhalotia

            Hi Pooja,

            So with help of DATEPARSE fuction i can convert 3-12-2015 into date format of 3/12/2015?

            • 3. Re: Converting text date field to numeric date field
              Mark Fraser

              So with help of DATEPARSE fuction i can convert 3-12-2015 into date format of 3/12/2015?

              Yes.

              Providing your using one of the data sources Pooja mentioned.

               

              The DATEPARSE function is available for non-legacy Microsoft Excel and text file connections, MySQL, Oracle, PostgreSQL, and Tableau data extracts data sources. The DATEPARSE function takes two parameters, “format” and “string.”


              Example - DATEPARSE('dd-MM-yyyy', [Date String])


              More help here >> http://kb.tableau.com/articles/knowledgebase/understanding-the-dateparse-function


              The other function which maybe of use is MAKEDATE()

              Full list here > Date Functions


              Cheers

              Mark

              • 4. Re: Converting text date field to numeric date field
                Ryan Rowland

                You could also use the "Change Data Type" function by right clicking on your text field (See attached).

                • 5. Re: Converting text date field to numeric date field
                  Tableau kumar

                  The below link tells how to covert Text to Date, similarly you can implement for Date&Time.

                   

                  Tableau BI: How to convert String to Date.

                   

                   

                  or

                   

                  The DATEPARSE function relies on the locale specified by your computer settings to recognize and display the strings that you want to convert. More specifically, the locale will affect whether a certain format can be recognized. This means that if a format is not supported by the locale, then you may see a null value or no value returned. For example, suppose you have the following string in your data:

                  12Sep2012:9:8:8.6546

                  The values returned from the DATEPARSE function for this string are different based on locale. In the English locale you will get a certain value, but for the Japanese locale you will get no value. In this case, no value is returned because the Japanese locale does not recognize “Sep.”

                   

                   

                  Datetime value in English localeDatetime value in Japanese locale
                  #9/12/2012 9:08:09 AM#-

                   

                  As with all dates, after you have used the DATEPARSE function to convert a string to a datetime type, by default Tableau will display the datetime value in the default format of your locale. If the locale changes, the result of the DATEPARSE function may display your new datetime value in a different format.

                  Commonly used formats

                  To get a better idea of how to format your string data, review the table below for examples of the DATEPARSE function for some commonly used date strings. Note: To avoid formatting issues related to locale, use the ISO formatted date literal as a best practice.

                  Date stringDATEPARSE functionEquivalent date literal in ISO 8061

                  Display value returned

                  (based on English locale and default formatting)

                  16.8.97DATEPARSE (“d.M.yy”, “16.8.97”)#1997-08-16#8/16/1997 12:00:00 AM
                  7/12/91DATEPARSE (“d/M/yy”, “7/12/91”)#1991-12-7#12/7/1991 12:00:00 AM
                  12Sep2012:9:8:7.6546DATEPARSE (“ddMMMyyyy:H:m:s.SSSSSS”, “12Sep2012:9:8:8.6546”)

                  Note: Fractional seconds, in excess of three digits, are truncated to the millisecond.

                  #2012-09-12T09:08:09#9/12/2012 9:08:09 AM
                  5:09DATEPARSE(“h:m”, “5:09”)#5:09:00#5:09:00 AM
                  97.11DATEPARSE(“yy.MM”, “97.11”)#1997-11#11/1/1997 12:00:00 AM
                  9743DATEPARSE(“yyMd”, “9743”)#1997-04-13#4/3/1997 12:00:00 AM
                  97-8-14 3:2:1DATEPARSE(“yy-MM-dd h:m:s”, “97-8-14 3:2:1”)#1997-08-14T3:02:01#8/14/1997 3:02:01 AM
                  19977345DATEPARSE (“yyyyMdHm”, 19977345”)#1997-07-03T04:05#7/3/1997 4:05:00 AM

                  To change how the new datetime value displays in the view, right-click the DATEPARSE calculation in the Data window, and select Default Properties > Date Format.