The date functions are useful for a variety of tasks, such as identifying the elapsed time between two events or a part of a date.
Many date functions in Tableau Public operate on dateparts. Dateparts are the small units of measurement of dates, such as a year, quarter, month, week, day, and the units of time, such as an hour, a minute, and a second. Writing date functions in Tableau Public is similar to writing date functions in ANSI SQL or Microsoft Excel with a big difference—dateparts need to be spelled out and enclosed in single quotation marks, which is a relatively simple design and use of punctuation for a programming language.
The most commonly performed analysis is the measurement of change over time, and the prevalence of date functions is high. Therefore, it is worth the time and effort to master them early on in your work with Tableau Public.
The date functions include the following functions:
- DATEADD: This adds numeric values, which can be hard-coded or variable, in a specific number of dateparts to a date.
- DATEDIFF: This calculates the elapsed number of specified dateparts between two dates.
- DATENAME: This produces the name of the specified datepart of a date. An example that demonstrates the implementation of this function is,
DATENAME('month', #7/21/2015#) = "July". - DATEPARSE: This turns a string into a date in the format that you specify.
- DATEPART: This is similar to DATENAME. It returns the datepart of a specified date but returns a numeric value. An example that demonstrates the implementation of this function is,
DATEPART('month', #7/21/2015#) = 7. - DATETRUNC: This rounds up a date to the first date of the datepart that you specify. An example that demonstrates how to implement this function is,
DATETRUNC('month, #7/21/2015#) = '7/1/2015'. - DAY: This returns the day of the month of a given date. An example that demonstrates how to implement this function is,
DAY(#7/21/2015#) = 21. - ISDATE: This tests whether a string that you enter is actually a date and produces a Boolean value (
trueorfalse). - MONTH: This produces the numerical month of a date.
- TODAY and NOW: These produce the
dateanddatetimeof the current moment by using the time settings on your computer. - YEAR: This produces the year of the current date.
In the following example, we will use the floods data to determine the number of years that have elapsed since the last major flood. Most of the countries have had many floods and not just one. Therefore, we used the maximum value of Date Began, which is the most recent date in the data set, and compared it with today's date.
We could have omitted the max from the Date Began, but for each country, Tableau Public would have then aggregated the elapsed time per country, and we really wanted to know how long it has been since the most recent flood, as shown in the following screenshot:
