The different Data Types that are returned when using Date Functions in Tableau
When using any function in Tableau we need to be aware of the Data Type of the resultant field for carrying out proper analysis.
This is especially true in case of the Date functions as they are many of them in Tableau.
In most cases we will be able to determine the resultant Data Type based on the name of the Date function.
The date functions in Tableau will result in different Data Types e.g. Date, Date & Time, Number (whole), String, Boolean.
NOTE: We will be covering only the most commonly used Date functions in this article.
Given below are the Date functions
DATEADD: This will add a particular number of date parts i.e. month, year to a starting date.
Syntax: DATEADD(date_part, interval, date)
This date function will by default always return a Date & Time Data Type.
To check if the time component is also present in this field drag it into the view in the Rows shelf and right-click select Exact Date.
Next right-click and select Discrete.
By default if the date mentioned in the calculation does not have a time component it will show up as 00:00:00.
DATEDIFF: This will return the number of date parts between two dates.
Syntax: DATEDIFF(date_part, date1, date2, [start_of_week])
This date function will by default always return a Number (whole) Data Type i.e. Integer.
Drag the date fields into the view in the Rows shelf and right-click select Exact Date. Next right-click and select Discrete.
Next drag and drop the DATEDIFF field into the Text of Marks card. Right-click and select Dimension.
DATENAME: This will return the date part of the specified date as a discrete string.
Syntax: DATENAME(date_part, date, [start_of_week])
This date function will by default always return a String Data Type.
Drag the date field into the view in the Rows shelf and right-click select Exact Date. Next right-click and select Discrete.
Next drag and drop the DATENAME field into the Rows shelf.
DATEPARSE: This will return formatted strings as dates.
Syntax: DATEPARSE(date_format, date_string)
This date function will by default always return a Date & Time Data Type.
Drag the DATEPARSE field into the view in the Rows shelf and right-click select Exact Date. Next right-click and select Discrete.
DATEPART: This will return the date part of the specified date as an Integer.
Syntax: DATEPART(date_part, date, [start_of_week])
This date function will by default always return a Number (whole) Data Type i.e. Integer.
Drag the date field into the view in the Rows shelf and right-click select Exact Date. Next right-click and select Discrete.
Next drag and drop the DATEPART field into the Text of Marks card. Right-click and select Dimension.
DATETRUNC: This will return the first occurrence of the specified date part. For example, when we truncate a date that is in the middle of the month at the month level, this function returns the first day of the month.
Syntax: DATETRUNC(date_part, date, [start_of_week])
This date function will by default always return a Date & Time Data Type.
Drag the date field into the view in the Rows shelf and right-click select Exact Date. Next right-click and select Discrete.
Next drag and drop the DATETRUNC field into the Rows shelf. Right-click and select Exact Date. Next again right-click and select Discrete.
DAY: This function is similar to DATEPART and it will return the day of the month (1–31) from the specified date as an Integer.
Syntax: DAY(date)
This date function will by default always return a Number (whole) Data Type i.e. Integer.
Drag the date field into the view in the Rows shelf and right-click select Exact Date. Next right-click and select Discrete.
Next drag and drop the DAY field into the Text of Marks card. Right-click and select Dimension.
NOTE: Similarly the Date Functions MONTH, QUARTER, WEEK and YEAR also will return a Number (whole) Data Type hence these functions will not be covered in this article.
NOTE: Similarly the Date Functions ISOQUARTER, ISOWEEK, ISO WEEKDAY and ISOYEAR will return the week-based date part (quarter, week, weekday, year) of a given date as an integer hence these functions will not be covered in this article.
ISDATE: This will check if the specified string expression is a valid date.
Syntax: ISDATE(string expression)
This date function will by default always return a Boolean Data Type i.e. either True or False.
Drag and drop the ISDATE field on the Rows shelf.
MAX: This will return the maximum date from the specified date or the maximum among two dates.
Syntax: MAX(expression) or MAX(expr1, expr2)
If a Date Expression is used in this function this date function will by default always return a Date Data Type.
Drag and drop the MAX field on the Rows shelf.
NOTE: Similarly the Date Function MIN also will return a Date Data Type (when a date expression is used) hence these functions will not be covered in this article.
NOW: This will return the timestamp as per the current local system date and time. NOW does not take an argument.
Syntax: NOW()
This date function will by default always return a Date & Time Data Type.
Drag the NOW field into the view in the Rows shelf and right-click select Exact Date. Next right-click and select Discrete.
TODAY: This will return today’s date as per the current local system date.
TODAY does not take an argument.
Syntax: TODAY()
This date function will by default always return a Date Data Type.
Drag the TODAY field into the view in the Rows shelf and right-click select Exact Date. Next right-click and select Discrete.
Apart from these Date functions there are some other date related functions that come under the Type Conversion category.
Given below are the Type Conversion Functions
DATE: This will convert the specified expression ( string or number) into a date.
Syntax: DATE(expression)
This function will by default always return a Date Data Type.
Drag the DATE field into the view in the Rows shelf and right-click select Exact Date. Next right-click and select Discrete.
DATETIME: This will convert the specified expression ( string or number) into a datetime.
Syntax: DATETIME(expression)
This function will by default always return a Date & Time Data Type.
Drag the DATETIME field into the view in the Rows shelf and right-click select Exact Date. Next right-click and select Discrete.
MAKEDATE: This will return a date value constructed from the specified numerical year, month, and date.
Syntax: MAKEDATE(year, month, day)
This function will by default always return a Date Data Type.
Drag the MAKEDATE field into the view in the Rows shelf and right-click select Exact Date. Next right-click and select Discrete.
MAKEDATETIME: This will return a datetime value constructed from the specified date and time.
Syntax: MAKEDATETIME(date, time)
This function will by default always return a Date & Time Data Type.
Drag the MAKEDATETIME field into the view in the Rows shelf and right-click select Exact Date. Next right-click and select Discrete.
MAKETIME: This will return a date value constructed from the specified hour, minute, and second.
Syntax: MAKETIME(hour, minute, second)
As Tableau does not support a time data type, only date time, this function will by default always return a Date & Time Data Type.
The date portion of the field will always be 30–12–1899.
I hope this article is useful for all Tableau users especially for anyone just starting their Tableau journey!!!
