What Is DATEPART Function In Tableau
The DATEPART function in Tableau is used to extract a specific part or component of a date or date-time value, such as year, quarter, month, week, day, hour, minute, or second.
DATEPART() Function in tableau is really helpful for those data source which only have the given Date in the data source, then you can calculate new column or field using the calculated Field in Tableau to calculate the day, month, year, week from that given date and design the charts accordingly in Tableau Desktop.
For Example: If you have user traffic data for the network (mobile network traffic data), with only given date format “2024/07/23”, then you can easily calculates others component of Date (like month, quarter, year and week) and accordingly you can design the user traffic visualization by month, quarter, year and week.
What Does The DATEPART() Function Do?
DATEPART function in tableau give you the specific interval of time from a given Date. example, if you would like to get the “weekday” from the given date then it will return the weekday of that given date.
Syntax Of DATEPART() Function:
DATEPART (date_part, date)
Explanation of syntax and arguments
- date_part: Specifies the time unit, This can be one of the following values: ‘day’, ‘hour’, ‘minute’, ‘second’, ‘week’, ‘month’, ‘quarter’, or ‘year’.
- date: It is a date of the period you want to extract specific part.
Note:
The date_part values ( ‘day’, ‘hour’, ‘minute’, ‘second’, ‘week’, ‘month’, ) should always be in small caps.
No Capital letter and even not first letter should be in capital.
If you will put any date_part values in capital letter it will through you the Error as shown below image
Key Features:
- The first argument is a string that specifies the part or component of the date or datetime value to extract.
- The second argument is the date or datetime value from which the component will be extracted.
Below are 5 examples for DATEPART() function:
Example #1: DATEPART Function To Extract Year From The Date
Lets take example for HR dataset we have “Date of birth” date of the employees and we would like to extract the “year” of birth from date. We can use below formula for DATEPART() in Tableau to get the birth year of employees.
DATEPART("year",[Date of Birth])Below is the output look like in table format,
You can see in below image. the red marked column is the calculated column to extract the Year from date field.
Note: If your date format is string in your dataset, you can use DATE() function inside the formula as show below syntax
DATEPART(“year”, DATE ([Date of Birth]) )
here we have use DATE() function for “Date of Birth” field
Also Read: DATEDIFF function in Tableau
Example #2: DATEPART Function In Tableau To Extract Month
In HR dataset we have “Date of birth” field for employees and we would like to extract the “month” from the date, we can use below formula for DATEPART in Tableau to get the birth month of the employees.
DATEPART("month",[Date of Birth])Below is the output:
You can see in below image. the red marked column is the calculated column to extract the month from date field.
Example #3: Extract Hour From The Date
In below example you can extract “hour” part from Datetime format field, however, in below we have used specific datetime format.
DATEPART('hour', #2022-02-15 10:30:00#)This returns the integer value of 10 as hour.
Example #4: Extract Week Of Year From The Date
Now we will extract week from the “Date of birth” field for employees and we can use below formula for DATEPART in Tableau to get the birth week of the employees.
DATEPART("week",[Date of Birth])Below image is the output for “Date of birth” field.
You can see in below image the red marked column is the calculated column to extract the week from date field.
Example #5: Extract Day Of Week From The Date
Now, we will extract weekday from the “Date of birth” field for employees and we can use below formula for DATEPART in Tableau to get the birth weekday of the employees.
DATEPART("weekday",[Date of Birth])Below image is the output, the red marked column is the calculated column to extract the weekday from date field.
This returns the integer value from 1 to 7 which specify the weekday Monday, Tuesday, Wednesday etc….
Conclusion
In each example, the DATEPART() function extracts a specific part or component of a date or datetime value. The resulting value can be used in calculations or displayed in a visualization.
It is important to ensure that the specified part or component matches the data being analyzed to avoid errors in extraction. Additionally, the values returned by the DATEPART() function may differ depending on the data source or database being used.