DATEPART in Tableau

What is DATEPART function in Tableau

The DATEPART function in Tableau is used to extract a specific part or component of a date or datetime value, such as year, quarter, month, week, day, hour, minute, or second.

Syntax of the 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 but 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.