The Tableau DATEPARSE is one of the date functions that will convert any field (in general string) to the date field based on the date_format and date_string arguments. This article shows how to use this Tableau DATEPARSE function to convert a field to the DATE field and what the alternative approaches (pre-built) with examples.
Generally, each Database has a unique approach to storing Date fields. In some cases, users deliberately store the DateTime in string format. In such a case, the desktop automatically converts those fields to date fields. However, in some cases, it may not, and to deal with them, use the Tableau DATEPARSE function.
The syntax of the Tableau DATEPARSE function is as shown below.
DATEPARSE(date_format, date_string)
In the above DATEPARSE function syntax, the first argument, i.e., date_format, describes how Tableau will arrange the date inside the date_string argument. The order of the first and second arguments should match. Otherwise, it simply returns empty. For instance, date_format = ‘yyyy-MM-dd’, the date_string has to be like ‘2020-12-31’. So, always change the date_format according to the string column.
Tableau DATEPARSE Function Examples
The Tableau DATEPARSE function returns the specifically formatted strings as a Date. Instead of appearing as the date Field, the column (Dimension) may occur as a string. Our job is to convert that string field (Abc) or column to the Date or DateTime (Calendar + Time Symbol) based on the requirement.
To demonstrate the Tableau DATEPARSE function, we will use a simple Employee table where the HireDate and HDate columns contain date and time. However, the data type is string (Abc).
Convert String to a Date within the DataSource
Within the Data Source, under the Fields section, clicking the Abc Type beside the HireDate will open the below show context menu. If you observe the image, by default, it checkmarked the string. So, please change it to Date & Time. Please refer to Date Functions and other Functions articles in Tableau.
Similarly, do the same for the HDate field.
Let me undo those steps to show more possibilities of the Tableau DATEPARSE function.
Convert String to a Date in the Data Shelf
As we kept the HireDate and HDate columns within the Data Source as strings, the Data Shelf shows them as strings. So, under the tables section, click the Abc Type beside the HireDate and select the Date & Time option. It will change them to the DateTime field.
Tableau DATEPARSE function to Parse a Date and Time from a String
To demonstrate this DATEPARSE Date function, we created a calculated Field for writing an expression. Use the Analysis Menu and select the Create Calculated Field.. option to do this. Next, add this HireCal field to the Rows shelf to show the result.
Parse Date from a String
Let me introduce a fundamental approach to converting a string to a date. If you observe the expression, we use a static one inside the Tableau DATEPARSE function, which will display the same date with the default time for each row of the HireCal column.
DATEPARSE('dd-mm-yyyy', '10-12-2020')
Parse Date & Time from a String
Please click the down arrow beside the HireCal and select the Edit option to change the calculated Field expression. This time, we pass the DateTime as the date_string and the appropriate format as the first argument.
DATEPARSE('dd.MMM.yyyy hh:mm:ss', '02.April.2022 10:30:00')
The following section explains a few Other Tableau DATEPARSE function options to convert string to Date.
How do you convert the string column to the date field?
In all the above examples, we used the static single date as the second argument. Let me use the actual string field to show the real-time work. The below expression will covert the HireDate string field to the Date Column.
DATEPARSE('MMM dd yyyy h:mma', [Hire Date])
If you observe the HireCal column, the Tableau DATEPARSE function works very well. It returns the dates, and there is also a Calendar + Time Symbol beside it. However, the expression has produced two NULL values because the SQL database considers Sep as September, whereas Tableau considers Sept as September.
So, create a new calculated field and use the string REPLACE() to replace the Sep word with Sept.
REPLACE([Hire Date], 'Sep', 'Sept')
Next, use this field as the second argument of the Tableau DATEPARSE function. If you look closely, there is a gap between Hire and Date in the previous expression, but not here.
DATEPARSE('MMM dd yyyy h:mma', [HireDate])
Similarly, change the H Date string Column to DateTime (HCal); the expression is as shown below.
DATEPARSE('MMMddyyyyh:mm', [H Date])
