Convert a Field to a Date Field
Each database stores Date fields in slightly different ways. Tableau does its best in interpreting the date fields, but sometimes a field will be imported to Tableau as a text string or numerical field. If this happens, there are a few steps we can take to resolve the issue.
Verifying Date Fields
A date field might appear as a string
Or as a string in the Data pane
When dates are interpreted as strings, you will lose all of the features and conveniences of working with date fields, such as drilling down, using date calculations, and switching between Continuous and Discrete measures.
If your dates aren't interpreted correctly, take the following steps in order:
The first step in resolving a date field interpretation issue is to make sure the data type is set to Date or Date & Time.
In the Data Source or Data pane, click the data type icon and change the data type to Date or Date & Time
Inspect the data in the view or Data Source pane. If you see many Null values, return the data type to String and proceed to Create a calculation using the DATEPARSE function to correct the issue.
Dates are stored in a nearly infinite array of formats. Some date fields have years before months, others separate the parts of the date with periods, and still others use a combination of formats. When Tableau cannot interpret a date field, it might be because the particular format cannot be translated.
The DATEPARSE function lets you clearly define which parts of your field are which parts of a date. In essence, you are creating a map that Tableau can use to translate the string into a date field. This map is referred to as the format.
Note: This function is available through the following connectors: non-legacy Excel and text file connections, Amazon EMR Hadoop Hive, Cloudera Hadoop, Google Sheets, Hortonworks Hadoop Hive, MapR Hadoop Hive, MySQL, Oracle, PostgreSQL, and Tableau extracts. Some formats may not be available for all connections.
Creating the DATEPARSE calculation
Inspect the current format of your date field. Note where months, years, hours, and days are displayed in your field. You will need this information for the DATEPARSE function.
A. Day of month
D. Hour, minute, second, millisecond, period
Right click your date field in the Dimensions pane and select Create > Calculated Field.
In the dialog, write the DATEPARSE function. The DATEPARSE function has two parts: the format and the string. The string is field you wish to convert, which must be a string data type.
The format is the guide Tableau will use to interpret the string as a date. Each part of a date or time string has a matching symbol, as seen in the table below. The format must exactly duplicate the way that the date is displayed. For example, if a single year code ("Y") is used when the string has a 2-digit year code ("97"), the calculation might return null values.
Note: The exception to the format exactly duplicating the displayed string is that long form parts of dates ("September") can be formatted with four symbols ("September" = "MMMM").
The format must include all spaces, hyphens, and other non-alphanumeric symbols in order to interpret the string correctly.
Date Field Symbols
Date Part Symbol Example String Example Format Year y 97, 2017 yy, y or yyyy Year of "Week of year" Y 1997 Y Time Zone Z, z -0800, PST-08:00, PST Z, ZZZZ, zzz Month M 9, 09, Sep, September M, MM, MMM, MMMM Week of year (1-52) w 8, 27 w, ww Quarter of year Q Q2, 2nd quarter QQQ, QQQQ Day of week E Mon, Monday EEE, EEEE Day of month d 1, 15 d, dd Day of year (1-365) D 23, 143 D, DD, DDD Period (am/pm marker) a AM, am, PM aa, aaaa Hour (1-12), Hour (0-23) h, H 1, 10, 16 h, hh, HH Minute m 8, 59 m, mm Second in a minute s 5, 05 s, ss Fractional second S 2, 23, 235, 2350 S, SS, SSS, SSSS Milliseconds in day A 34532 AAAAA
For a more complete list of date symbols, please see the Formatting Date and Times Overview on the International Components(Link opens in a new window) for Unicode page.
Note: Some date formats are not supported by all databases and file types.
Check your format against the string displayed in Tableau. If the symbols and formatting is correct, select OK to create the new calculated field. The calculated field will act as a date field in your view.
For .hyper extract data sources, the symbols are defined by the Unicode Consortium. For more information, go to Format syntax in DATEPARSE function for extract data sources to see a the subset of field types and symbols that can be used with the DATEPARSE function in .hyper extracts.
The DATEPARSE function relies on the locale specified by your computer settings to interpret and then 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 might see a null value or no value returned. For example, suppose you have the following string in your data:
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.”
|English locale||Japanese locale|
|#9/12/2016 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 might display your new datetime value in a different format.
Note: Because of a Jet limitation, the DATEPARSE function cannot correctly identify locale information specified by your computer settings in extracts created from Access data sources. To resolve this issue, consider exporting your Access data to Excel.
If the DATEPARSE function is not available for the data that you're working with, or the field you are trying to convert is a number data type, you can use the DATE function instead.
The DATE function converts a number, string or date expression to a date type. When you create a calculation that uses the DATE function, Tableau creates a new field in your Tableau data source that allows you to interact with your date data as a date. To successfully produce date values from a number, string, or date expression using the DATE function, Tableau needs to be able to interpret the components of the string into date parts. After the components of the date are identified, Tableau uses the computer locale to determine the default format of the date.
For example, suppose the table you're working with contains a column of date data that is called "Original Date." The "Original Date" column is a string type.
In this case, you can create a calculated field called "New Date" that uses an expression in a DATE function to convert the string values in the "Original Date" field into date values.
For this example, the date expression is comprised of the LEFT function to isolate the day component, the MID function to isolate the month component, and the RIGHT function to isolate the year component.
DATE (LEFT([Original Date], 2) + "/" + MID([Original Date],3,3) + "/" + RIGHT([Original Date],4))
The "New Date" calculation produces the following column:
In this example, the new date values are based on an English locale and default formatting.
STR() Function Ignores the Default Date and Number Formatting(Link opens in a new window) (Tableau Knowledge Base)