_edited_edited.png)
In this blog, I am going to describe the Date Functions provided by Tableau.
Dates are a common element in many data sources. If a field contains recognizable dates, it will have a date or date time data type. When date fields are used in the visualization they get a special set of functionalities, including an automatic date hierarchy drill down, date-specific filter options and specialized date formatting options.
Date functions allows you to manipulate dates in your data source. You can make use of the “Create Calculated Field” option from the drop-down menu near the search bar to access all these Date Functions. A small window will appear on the screen which are in-built with all functions available in Tableau. From the list, you can select which ever function you need. There is also a brief description for the selected function on the right side.
DATE
Type conversion function that changes string and number expressions into dates, as long as they are in a recognizable format.
Returns a date given a number, string, or date expression.
Syntax: DATE (expression)
Output: Date
1. DATEADD
Adds a specified number of date parts (months, days, etc..) to the starting date.
Returns the specified date with the specified number interval added to the specified date part of that date. For example, adding three months or 12 days to a starting date.
Syntax: DATEADD (date_part, interval, date)
Output: Date
2. DATEDIFF
Returns the number of date parts (weeks, years, etc..) between two dates.
Returns the difference between date1 and date2 expressed in units of date part. For example, subtracting the dates someone entered and left a band to see how long they were in the band.
Syntax: DATEDIFF(date_part, date1, date2, [start_of_week])
Output: Integer
3. DATENAME
Returns the name of the specified date part as a discrete string.
Returns date_part of date as a string.
A very similar calculation is DATEPART, which returns the value of the specified date part as a continuous integer. DATEPART can be faster because it is a numerical operation.
By changing the attributes of the calculation’s result (dimension or measure, continuous or discrete) and the date formatting, the results of DATEPART and DATENAME can be formatted to be identical.
An inverse function is DATEPARSE, which takes a string value and formats it as a date.
Syntax: DATENAME(date_part, date, [start_of_week])
Output: String
4. DATEPART
Returns the name of the specified date part as an integer.
Returns date_part of date as an integer.
A very similar calculation is DATENAME, which returns the name of the specified date part as a discrete string. DATEPART can be faster because it is a numerical operation. By changing the attributes of the field (dimension or measure, continuous or discrete) and the date formatting, the results of DATEPART and DATENAME can be formatted to be identical.
An inverse function is DATEPARSE, which takes a string value and formats it as a date.
Syntax: DATEPART(date_part, date, [start_of_week])
Output: Integer
5. DATETRUNC
This function can be thought of as date rounding. It takes a specific date and returns a version of that date at the desired specificity. Because every date must have a value for day, month, quarter, and year, DATETRUNC sets the values as the lowest value for each date part up to the date part specified. Refer to the example for more information.
Truncates the date to the accuracy specified by the date_part. This function returns a new date. For example, when you 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])
Output: Date
6. DAY
Returns the day of the month (1-31) as an integer.
Returns the day of the given date as an integer.
See also WEEK, MONTH, QUARTER, YEAR, and the ISO equivalents.
Syntax: DAY (date)
Output: Integer
7. ISDATE
Checks if the string is a valid date format.
Returns true if a given string, is a valid date.
The required argument must be a string. ISDATE cannot be used for a field with a date data type—the calculation will return an error.
Syntax: ISDATE (string)
Output: Boolean
Example: ISDATE(09/22/2018) = true ISDATE(22SEP18) = false
8. MAX
MAX is usually applied to numbers but also works on dates. Returns the maximum (most recent) of a date field or two dates.
MAX(expression) is treated as an aggregate function and returns a single aggregated result. This will display as AGG([calculation name]) in the viz and will not have a date hierarchy.
MAX(expr1, expr2) compares the two values and returns a row-level value. For dates, that value will be a date, and the results will retain the date hierarchy.
Returns Null if any argument is Null.
Syntax: MAX(expression) or MAX(expr1, expr2)
9. MIN
MIN is usually applied to numbers but also works on dates. Returns the minimum (earliest) of a date field or two dates.
MIN(expression) is treated as an aggregate function and returns a single aggregated result. This will display as AGG([calculation name]) in the viz and will not have a date hierarchy.
MIN(expr1, expr2) compares the two values and returns a row-level value. For dates, that value will be a date, and the results will retain the date hierarchy.
Returns Null if any argument is Null.
Syntax: MIN(expression) or MIN(expr1, expr2)
10. MONTH
Returns the month of the given date as an integer.
See also DAY, WEEK, QUARTER, YEAR, and the ISO equivalents.
Syntax: MONTH (date)
Output: Integer
11. TODAY
Returns the current local system date.
TODAY does not take an argument.
See also NOW, a similar calculation that returns a datetime instead of a date.
If the data source is a live connection, the system date could be in another time zone.
Syntax: TODAY ()
Output: Date
12. WEEK
Returns the week of the given date as an integer.
See also DAY, MONTH, QUARTER, YEAR, and the ISO equivalents.
Syntax: WEEK (date)
Output: Integer
13. YEAR
Returns the year of the given date as an integer.
See also DAY, WEEK, MONTH, QUARTER, and the ISO equivalents.
Syntax: YEAR (date)
Output: Integer