The Tableau DATEPART is one of the date functions that help to extract the part of a given date and returns the integer value based on the first argument, i.e., date_part.
For example, if you pass the year as the first argument, the DATEPART function returns the year from Date1. This article shows how to use this DATEPART function in Tableau and gives examples of possible extraction techniques, including the parameter solution.
Although there are dedicated functions like YEAR(), MONTH(), DAY(), etc, to get the date parts, the DATEPART function is the most advanced and gives more control to the developer. The syntax of the Tableau DATEPART function is as shown below.
DATEPART(date_part, date, start_of_week)
In the above Tableau DATEPART syntax, the function’s date_part (first) argument accepts day, year, month, quarter, weekday, dayofweek, week, hour, minute, second, etc. The start_of_week argument is optional; you can use this to mention the day from which the week has to start. For instance, ‘Monday’, ‘Sunday’, etc.
Tableau DATEPART Function Examples
To demonstrate this DATEPART Date function, we use a simple Employee table with a Hire Date column, and we use that column to extract each part of the date.
To use this built-in Tableau DATEPART function, we must create a calculated Field so that you can write an expression. So, right-click the empty space within the Measures shelf and select the Create Calculated Field.. option.
Extract Year from the Date
In this Tableau DATEPART function example, we will extract the year of the Hire Date to see which year each employee was recruited into the company. To do this, please write the below expression with the first argument as year and HireDate as the second argument. Since the first argument is a string, you must put it in single or double quotes.
DATEPART('year', [Hire Date])
Next, let me add that calculated Field to the table. If you look at the output, you can clearly see the Year.
Remember, if there is a comma (thousand separators), right-click the cell and click the format. Next, go to Numbers -> Number (Custom) -> uncheck the Include Thousand Separator option. For more information, refer to the date functions and other functions.
Extract the Day Number from the Date
If you want to extract or return the Day number from the Hire date, then similar to the above, we have created another calculated field with the day as the Tableau DATEPART function first argument.
DATEPART('day', [Hire Date])
Use this same technique to extract the month, week, hour, etc. Creating the required calculated fields with the appropriate date_part value for each part would be best.
Extract the Month Number from the Date
To return the month number from 1 to 12, use the below expression
DATEPART('month', [Hire Date])
Extract Hour from the Date
Use the expression below to return the Hour from 1 to 24 from the date and time.
DATEPART('hour', [Hire Date])
Tableau DATEPART function using parameter
If you want to allow the users to select the required datepart, create a parameter with a list of items instead of making multiple calculated fields for each DATEPART expression. To do so, right-click the Measures shelf empty space and select the Create Parameter option in Tableau.
It will open the following window. Let me select the Data type as a String. Next, add a list of parameter values representing the date_part, such as day, year, month, hour, minute, dayofyear, and weekday.
Next, create a calculated field, UserDatePart, with the first argument as a parameter.
DATEPART([ParamDatePart], [Hire Date])
Add the UserDatePart to the table, click the down arrow beside the ParamDatePart parameter, and choose the Show Parameter option from the context menu.
Let me change the default Parameter value of month to minute. The Tableau DATEPART extracts and returns the Minutes from the HireDate.
