Tableau has many date functions, all of which serve their own purpose and can be extremely powerful when you know how to use them.
However, with great options can come great confusion. From datepart to dateparse a lot of them sound the fairly similar and also appear to do relatively similar things. This blog post will breakdown some of the key date functions, how to use them and what they do, so you know which function to use next time you are doing some date analysis in tableau.
Date Functions for Creating a Date Field or Value
DATE
What is the syntax?
DATE(“expression”)
What does it do/When should I use it?
The DATE function converts any date layout to the date format that is recognised by tableau (yyyy-mm-dd e.g. 2020-10-15). This function can identify almost all date formats to convert, but you can use the dateparse function for more custom formats. Alternatively, you can also enter a date field as the expression, and again this will convert all dates in that field to the format yyyy-mm-dd.
Example: DATE(“September 22, 2018”) = 2018-09-22
DATEPARSE
What is the syntax?
DATEPARSE(format, string)
What does it do/When should I use it?
The DATEPARSE function is very similar to the DATE function - it converts a date expression into the format that tableau identifies as a date. The difference here is that the DATEPARSE function is more flexible, in that you can specify any date format that may not be identified or recognised in the DATE function.
Example: DATEPARSE("MMMM DD, YYYY", "September 22, 2018") = 2018-09-22
A list of all the date formats that you can use for this function and are recognised in tableau can be found here.
MAKEDATE
What is the syntax?
MAKEDATE(year, month, day)
What does it do/When should I use it?
This function will return a date when you enter numeric values for the date parts year, month and day. This has similar functionality to the DATE and DATEPARSE functions but requires all the inputs to be numerical values.
Example: MAKEDATE(2018, 9, 22) = 2018-09-22
A few date functions in tableau have a 'date_part' syntax, this is essentially the part of the date you want e.g. day, month, year etc. A list of all the date parts in tableau and their respective syntax can be found in the link here.
Date Functions for Extracting Part of a Date
DATEPART
What is the syntax?
DATEPART('date_part', date, [start_of_week])
What does it do/When should I use it?
The datepart function returns a 'part' of a specified date field as an integer, as defined by the date_part input. So it will return the number that corresponds to the specified date part - the number that corresponds to the day of the week, the day, the quarter, month etc.
Example - DATEPART('month', DATE('2010-10-15')) = 10 as October is the 10th month.
DATETRUNC
What is the syntax?
DATETRUNC('date_part', date, [start_of_week])
What does it do/When should I use it?
This function truncates a date by the ‘date_part’ and returns the first instance or date of the truncation as a new date.
Example - DATETRUNC('month', #2010-10-15#)= 2010-10-01 - this returns the first day of the month.
DATENAME
What is the syntax?
DATENAME('date_part', date, [start_of_week])
What does it do/When should I use it?
This function returns the name of the part of the date (date part) of a date or date field. The output is therefore a string e.g. 'May', 'Tuesday' etc.
Example: DATENAME('weekday', DATE('2018-09-22')) = Saturday
NOTE since the output of this function is always a string, calling the 'day' of a specified date or date field will return the number as a string e.g.:
DATENAME('day', DATE('2018-09-22')) = 22 - but tableau will recognise 22 as a string so you won't be able to create any numeric calculations with this.
Date Functions to Perform Calculations on Dates
DATEADD
What is the syntax?
DATEADD('date_part, interval, date)
What does it do/When should I use it?
This function allows you to add any interval to a specified date or date field. This is particularly useful when you want to calculate date projections e.g. if it takes 10 days for orders to be delivered, you can calculate the projected delivery date using this function. You can also subtract an interval from a date by using a negative number for the interval part of the function.
Example: DATEADD('month', 1, DATE('2018-09-22)) = 2018-10-22
DATEADD('day', -5, [order date]) will return the date that is 5 days before the order date, for each order date.
DATEDIFF
What is the syntax?
DATEDIFF(date_part, start_date, end_date, [start_of_week])
What does it do/When should I use it?
This function allows you to calculate the difference between two specified dates or date fields, where the difference is expressed in units of the datepart. This is particularly useful for calculating the duration of things e.g. you can calculate how long delivery actually took for each order by finding the difference between the order date and delivery date expressed by days. This is a great way to identify whether any orders took longer than the projected/expected date.
Closing Words
Each date function in tableau can be very useful in allowing you to generate key insights when working with dates. From manually creating dates that tableau can identify, to performing calculations on dates to help with projections etc, the list of outcomes is endless, regardless of how your dates are structured.