What is DATETRUNC() function in Tableau
The DATETRUNC() function in Tableau is used to truncate a date (shorten the duration) or datetime value to a specific level of granularity. Such as year, quarter, month, week, day, hour, minute, or second. This function is commonly used when working with time-series data and allows you to aggregate data at different levels of granularity.
Syntax for DATETRUNC:
DATETRUNC(date_part, date_expression)
Syntax explanation below:
- date_part: This is a string value that specifies the level of precision to which the date/time value should be truncated. The available options include: “year”, “quarter”, “month”, “week”, “day”, “hour”, “minute”, and “second”.
- date_expression: This is the date/time value that you want to truncate. This can be a field in your data source or a calculated expression.
Let’s take one simple example to explain how DATETRUNC works. Let’s say you have a dataset that includes a date field called “Order Date”. If you want to aggregate your data by month, you could use the following calculated field:
DATETRUNC('month', [Order Date])This would return a new date value for each row in your dataset that represents the first day of the month for the corresponding “Order Date”.
You can use this calculated field to create visualizations like bar chart, combined chart, that show the total sales or other metrics by month.
Below are more 5 examples use to explain in details for your understanding using the DATETRUNC() function:
Example #1 DATETRUNC in Tableau: Truncate a date to the beginning of the year:
In Below example, DATETRUNC() function will give you the beginning of year date, i.e 1st January 2022.
DATETRUNC('year', #2022-02-15#)This function will returns the date value #2022-01-01#.
Example #2 DATETRUNC in Tableau: Truncate a datetime to the beginning of the hour:
In this example, DATETRUNC() function will give you the beginning of hour, i.e 10:00:00,
DATETRUNC('hour', #2022-02-15 10:30:00#)This function output will be the datetime value #2022-02-15 10:00:00#.
Example #3: Truncate a date to the beginning of the month:
Below example, DATETRUNC() function will give you the beginning of month, like 1st Feb 2022,
DATETRUNC('month', #2022-02-15#)This function output will be the date value #v#.
Example #4: Truncate a datetime to the beginning of the day:
Below example, truncate datetime function will give you the beginning of the day meaning at midnight time value like 00:00:00,
DATETRUNC('day', #2022-02-15 10:30:00#)This returns the datetime value #2022-02-15 00:00:00#.
Example #5: Truncate a date to the beginning of the week (Sunday):
Below example, truncate date function will give you the beginning of the week and here it is Sunday. in below date 15th Feb 2022 is Tuesday but with “Week” option it will give the Sunday date that is 13th Feb 2022.
DATETRUNC('week', #2022-02-15#)This returns the date value #2022-02-13#.
In each example, the DATETRUNC() function truncates a date or datetime value to the specified level of granularity.
The resulting value can be used in calculations or displayed in a visualization. It is important to ensure that the specified level of granularity matches the data being analyzed to avoid errors in truncation.