Date functions are calculations used to manipulate date fields in our Tableau data source. For example, you may have a data source containing the Order date (date when an order is placed) and the Shipping date (date when an order is shipped to the customer). To compute, the time it takes to ship orders – you can use the DATEDIFF function, which allows you to compute the difference between two dates in the units specified on the date_part.
Syntax: DATEDIFF (date_part, date1, date2, [start_of_week])
Therefore, in this article – I’ll be sharing some of the date functions you can use to manipulate date field in Tableau.
Date functions available in Tableau
1. DATEADD Function
This function returns the specified date, with the number specified on the interval added to the specified date part of that date.
Syntax:
DATEADD (date_part, interval, date)
Example:
To add four months to the current date, I can use this function as shown below.
DATEADD (‘month’, 4, '16/10/2021') = '16/02/2022'
2. DATEDIFF Function
This function returns the difference between two dates, expressed in the units specified on the date part.
Syntax:
DATEDIFF (date_part, date1, date2, [start_of_week])
Example:
To compute the difference in weeks between the two dates.
DATEDIFF (‘week’, '10/10/2021', '12/10/2021', ‘monday’) = 1 (the two dates are on different weeks)
DATEDIFF (‘week’, '10/10/2021', '12/10/2021', ‘sunday’) = 0 (the two dates are on the same week)
Note: The [start_of_week] part is optional, and therefore when not specified – its determined by the data source.
3. DATENAME Function
This function returns the date_part of a date field as a string.
Syntax:
DATENAME (date_part, date, [start_of_week])
Example: DATENAME (‘month’, '27/12/2020') =’December’
DATENAME (‘year’, '27/12/2019') = ‘2019’
4. DATEPARSE Function
This function returns a date_string as a date.
Syntax:
DATEPARSE (date_format, date_string)
Example:
DATEPARSE (‘yyyy/MM/dd’, '2021-10-16') = ‘October 16, 2021’
5. DATEPART Function
This function returns the date_part of a date as an integer.
Syntax:
DATEPART (‘date_part’, date, [Start_of_week])
Example:
DATEPART (‘month’, '15/05/2021') = 5
DATEPART (‘year’, '27/01/2021') = 2021
6. DATETRUNC Function
This function truncates the specified date to the accuracy specified in the date_part.
Syntax:
DATETRUNC (‘date_part’, date, [start_of_week])
Example:
DATETRUNC (‘month’, '27/09/2021') = '01/09/2021' (truncates the date from 27th to the start of the month).
DATETRUNC (‘quarter’, '17/03/2021') = ‘01/01/2021’ (truncates the date from March 17th (which falls in quarter 1) to the date when the first quarter began).
7. DAY/WEEK/MONTH/QUARTER/YEAR Functions
These functions return the date part of the given date as an integer.
Syntax:
DAY (date)
WEEK (date)
MONTH (date)
QUARTER (date)
YEAR (date)
Example:
DAY ('16/10/2021') = 16
WEEK ('16/10/2021') = 42
MONTH ('16/10/2021') = 10
QUARTER ('16/10/2021') = 4
YEAR ('16/10/2021') = 2021
8. MAKEDATE Function
This function returns a date value constructed from the specified year, month, and day.
Syntax:
MAKEDATE (year, month, day)
Example:
MAKEDATE (2021, 10, 17) = '17/10/2021' (October 17th, 2021)
9. MAKEDATETIME Function
This function returns a datetime that combines a date and a time.
Syntax:
MAKEDATETIME (date, time)
Example:
MAKEDATETIME (‘29/09/2021’, '08:35:00') = '29/09/2021 08:35:00 AM'
10. MAKETIME Function
This function returns a date value constructed from the specified hour, minute, and second.
Syntax:
MAKETIME (hour, minute, seconds)
Example:
MAKETIME (18, 05, 58) = '18:05:58'
11. MAX/MIN Functions
These functions return the maximum or minimum date of the two dates in comparison.
Syntax:
MAX (date1, date2)
MIN (date1, date2)
Example:
MAX ('21/02/2021', '03/10/2021') = '03/10/2021'
MIN ('21/02/2021', '19/11/2018') = '19/11/2018'
12. NOW Function
This function returns the current local system date and time.
Syntax:
NOW ()
Example:
NOW () = '16/10/2021 18:15:21' (date and time of writing this article)
13. TODAY Function
This function returns the current date.
Syntax:
TODAY ()
Example:
TODAY () = '16/10/2021' (date of writing this article)
More date functions can be accessed on the Tableau calculated field editor – by selecting Date on the side menu.
I hope this article was helpful to you. To receive more of the Tableau tips, kindly join our mailing list by subscribing below.
If you like the work we do and would like to work with us, drop us an email on our Contacts page and we’ll reach out!
Thank you for reading!
