In this blog, we are going to be covering the 5 most common Tableau date functions: DATEPART, DATENAME, DATETRUNC, DATEADD and DATEDIFF.
DATEPART uses the configuration DATEPART(‘date_part’,[Date]) returning an integer for the date_part specified e.g. year, quarter, month, weekday, week, day
DATENAME works very similar to DATEPART with the configuration DATENAME(‘date_part’,[Date]), the only difference between DATEPART and DATENAME is that DATENAME month and weekday returns a string e.g. September, Thursday.
Difference between DATEPART and DATENAME
DATETRUNC allows you to truncate your date field with the result of your calculation being in the date time format. It follows the configuration DATETRUNC(‘date_part’,[Date]).
Notice below for the date_part 'year' the DATETRUNC calculation returns the first day of January and this is consistent even when it is February. The date_part 'month' also returns the first day of every month. However for the date_part 'day' Tableau returns the exact date seen in [Order Date] this is because [Order Date] is at the day level, this is the lowest level of granularity.
DATEADD allows to add an interval to any date and it returns the result in a date time format, it has the configuration DATEADD(‘date_part’, integer,[Date]). You can take away from a date by making the integer a minus(-1, -2, -2 etc.).
Example: We want to create a field for the expected delivery date, we know after an after an order it takes 5 days for a delivery to be delivered. This is how we can do this with the DATEADD function.
Create a calculated field with the formular DATEADD('day',5,[Order Date]) and bring both the order date and expected arrival date to column. There you have it, you have calculated the expected arrival date of an order.
DATEDIFF allows you to calculate the difference between two dates by your chosen date_part. It has the configuration DATEDIFF(‘date_part’,[Start_Date],[End_Date]).
Example: We want to work out how many days it takes between an item being ordered and shipped.
DATEDIFF('day',[Order Date],[Ship Date])
And there you have it, we have covered the 5 most commonly used Date Functions