Date calculations are useful if you want to manipulate dates in your data source. To get you started I’m going to cover five of them in this blogpost:
- DATENAME
- DATEPART
- DATETRUNC
- DATEADD
- DATEDIFF
Before I dive deeper into each function let me clarify a term I’m going to use throughout this blog: date parts. A date part is just that: the part of a date. It could be a day, a week, a year etc. For example the date part ‘year’ of 20/06/2018 is 2018.
Most syntaxes include start of the week, but you don’t have to define that in each calculation and I haven’t done it in this post.
See the calendar below for reference.
DATENAME
The DATENAME function is very simple and returns the date part as string. This comes in handy when you’d like to know what day of the week 7 August 2018 is.
Syntax: DATENAME(date_part, date, [start_of_week])
Example:
DATEPART
DATEPART is similar to DATENAME, but it returns an integer for the date part instead of a string. Therefore Tableau interprets the result as Measure, but we don’t want to aggregated anything so we can just move it to Dimensions. It helps you to answer questions such as, ‘If the week starts on Sunday, how many days into the week is 07/08/2018?’ Note: When the date_part is ‘weekday’, the ‘start_of_week’ parameter is ignored. This is because Tableau relies on a fixed weekday ordering to apply offsets. In our case Tableau defines the start of the week as Sunday and therefore counts 3 days.
Syntax: DATEPART(date_part, date, [start_of_week])
Example:
DATETRUNC
You can use this function to truncate your date to the accuracy specified by the date part. The week trunc for example returns the first day of the week that our Order Date falls into. In this case our week begins on Monday 6th August.
Syntax: DATETRUNC(date_part, date, [start_of_week])
Example:
DATEADD
DATEADD adds an increment to the specified date and returns a new date. In other words, you can calculate what date it is 72 days from 07/08/2018.
Syntax: DATEADD(date_part, interval, date)
Example:
DATEDIFF
Datediff comes in handy when you’re interested in the difference between two dates. Again, you can define your date part, but this time you also add a start date and an end date to the mix. You’ll have to aggregate the result as average.
Syntax: DATEDIFF(date_part, start_date, end_date, [start_of_week])
Example:
That’s it and I hope you found my introduction to date calculations useful!