Updated March 15, 2023
Introduction to Tableau Date Functions
Tableau Date Functions are inbuilt functions present in tableau, enabling the user to manipulate the data records in a data source. These date functions allow users to create the records’ basic date operations and complex date expressions. Tableau provides many date functions like DAY, MONTH, YEAR, DATEDIFF, DATEPART, DATEADD, DATETRUNC, DATENAME, MAKE DATE, MAKE TIME, NOW, TODAY, etc.
Creation of Input for Tableau Date Functions
Each data source in tableau, which points to a database where records are stored, can store data fields differently. First, the fields are imported to the tableau as a text string or numerical value. Then, these interpretations follow the tableau’s steps to distinguish and differentiate between the date and other fields.
These steps can be summarized as follows:-
1. Verification of Date Fields
Verifications are required to distinguish and identify if the records contain the date, this can be done by identifying if the date string is present in the connection source or if the string is visible in the dimension plane. This step help in identifying non-date records from date records.
2. Change the Data Type of the Field
The next step in this process is when the data needs to be corrected, e.g., if the record contains much null value. Finally, these fields are transformed to string type in nature and fed to the Dateparse function.
3. Parsing the Date Format
There are infinite array formats in the data records; The Dateparse method defines the parts of the date. This creates a map for tableau to translate the string fields to date format. This map is called Format for tableau.
4. Defining the Date Function Expression
If the data being used is not of string type and belongs to any other format like numeric, the DATE function is used to convert the expression to a date type format. Tableau creates a new date field in the data source.
Different Types of Tableau Date Functions
1. DAY – The DAY function returns the day number from the Date String.
The syntax is as follows:- DAY(Date)
2. MONTH – The MONTH function returns the month number from a given date in the tableau.
The syntax is as follows:- MONTH(Date)
3. YEAR – The YEAR function returns the year from the given date.
The syntax is as follows:-YEAR(Date)
4. DATEDIFF – Returns the difference between the dates defined in the parameters.
The syntax is as follows: – DATEDIFF(depart, date1, date2, [start of week (optional)].
5. DATEPART – Returns the date part of a date as an integer.
The syntax is as follows DATEPART(depart, date, [start_ of_ week(optional)])
6. DATEADD – Returns the date with the interval specified to be added to the data fed as input.
The syntax is as follows:- DATEADD(depart, interval, date)
7. DATETRUNC – The DATETRUNC truncates the specified date to the accuracy specified by the date part. This function returns a new date as output.
The syntax is as follows:- DATETRUNC(depart, date, [start_ of_ week(optional)])
8. DATENAME – It returns the date part component of the supplied date as a string for its output.
The syntax is as follows:- DATENAME(depart, Date, [start_ of_ week(optional)])
9. MAKE DATE – The MAKE DATE function returns a visualization of the date value developed from the input year, month, and date.
The syntax is as follows:- MAKE DATE(Year, Month, Day)
10. MAKE TIME – The MAKE TIME function returns a visualization of a date value which is a result o the provided hour, minute, and second reference.
The syntax is as follows:- MAKE TIME(hour, minute, second)
11. NOW – The Now function returns the current date and time corresponding to the PC the user is logged on to.
The syntax is as follows:- NOW()
12. Today – Returns the current date as an output for the assigned program.
The syntax is as follows:- TODAY()
13. MAX – Returns the maximum value of the compared entities; this function is generally used to compare numeric expressions; however, the same is also valid for a date. For null exception Null is granted (MAX(date1, date2))
14. MIN – Returns the maximum value of the compared entities; this function is generally used to compare numeric expressions; however, the same is also valid for a date. For null exception Null is granted (MIN(date1, date2))
Examples of Date Functions in Tableau
| Function | Example | Output |
| Day | DAY(#2019-06-03#) | 3 (output is an integer equivalent to the day in the provided data) |
| Month | MONTH(#2019-06-03#) | 6 (output is an integer equivalent to the month in the provided data) |
| Year | YEAR(#2019-06-03#) | 2019 (output is an integer equivalent to the year in the provided data) |
| Date diff | DATEDIFF(‘week’, #2019-06-01#, #2019-06-04#, ‘Monday’) | 1 (because when the start of the week is Monday, then 04 June (Tuesday) and 01 June (Saturday) are in different weeks ) |
| DATEDIFF(‘week’, #2019-06-01#, #2019-06-04#, ‘Sunday’) | 0((because when the start of the week is Sunday, then 04 June (Tuesday) and 01 June (Saturday) are in the same week weeks ) | |
| DATEADD | DATEADD(‘month’, 5, #2019-06-01#) | 1/11/2019 (5 months are added to the date ) |
| DATEPART | DATEPART(‘year’, #2019-06-01#) | 2019( returns the requested depart as an integer) |
| DATEPART(‘month’, #2019-06-01#) | 6 ( returns the requested depart as an integer) | |
| DATETRUNC | DATETRUNC(‘quarter’, #2019-06-01#) | 4/1/2004 00:00:00 AM (truncates date to the limit specified by the depart) |
| DATENAME | DATENAME(‘year’, #2019-06-01#) | 2019(return the date part of the date as a String) |
| MAKEDATE | MAKEDATE(2019, 6, 3) | #03 June 2019# (function returns a constructed date from the supplied input) |
| MAKE TIME | MAKE TIME(13, 40, 25) | #13:40:25# (function returns a constructed time from the supplied input) |
| NOW | NOW( ) | 2019-06-03 1:10:45 AM (current date and time value are printed) |
| TODAY | TODAY( ) | 2019-06-03 (current date is printed) |
| MAX | MAX(#2019-06-01#, #2019-06-04#) | 6/4/2019 12:00:00 AM (maximum value among the option is printed) |
| MIN | MIN(#2019-06-01#, #2019-06-04#) | 6/1/2019 12:00:00 AM (Minimum value among the option is printed) |
Conclusion
The Date functions in the tableau are used to carry out various logical and arithmetical operations with dates. The output type of functions determines the results of the functions. Some functions may provide the same logical functions; however, the difference may be observed among the output types, as seen in the DATEPART and Year function in the table above.
Recommended Articles
This is a guide to the Tableau Date Functions. Here we discuss the concept, example, and tableau’s different types of date functions. You can also go through our other Suggested Articles to learn more –
