Tableau Date Functions

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 –

1. Tableau Commands
2. HBase Commands
3. What is Tableau?
4. Is Tableau Free
5. Guide to Date Functions in Hive
6. Guide to PostgreSQL Date Functions