One of the slightly confusing concepts in Tableau would be the DATEPART and DATETRUNC functions. The two functions appear to provide similar functions, however, there are still important distinguishing features between the two.
The syntax of the two are:
DATEPART(date_part, date, [start_of_week])
DATETRUNC(date_part, date, [start_of_week])
At first glance, the function’s requirements are exactly the same. So how are they different?
The output that they provide.
DATEPART returns an integer (but discrete and a blue pill).
Ex.
DATEPART('year', 2024-01-28) = 2024
DATEPART(‘month’, 2024-01-28) = 1
DATEPART(‘day’, 2024-01-28) = 28
DATETRUNC returns a truncated date to the level specified in the field (continuous and a green pill).
Ex.
DATETRUNC('year', 2024-05-28) = 2024-01-01
DATETRUNC(‘month’, 2024-05-28) = 2024-05-01
DATETRUNC(‘day’, 2024-05-28) = 2024-05-28
Hence, DATETRUNC returns something similar to as if it was rounding down the date. The function returns a date value with the lowest value based on the date_part specified. It is important to realize that when DATETRUNC is used, aggregation will be used.
Photo by Aron Visuals on Unsplash