Tableau – Date Functions

Date functions in Tableau are used to manipulate dates in your data source, such as year, month, date, day, and time. There are several built-in functions in Tableau that you can use for this purpose. In this document, we will go through the various date functions and their syntax, along with some examples.

DATEADD

This function returns the specified date with the specified number interval added to the specified date_part of the date. The syntax is as follows:

DATEADD(date_part, interval, date)

For example, DATEADD(‘month’, 3, #2019-09-17#) would return 2019-12-17 12:00:00 AM.

DATEDIFF

This function returns the difference between two dates expressed in units of the date part. The start of the week can be adjusted to the day a user needs to. The syntax is as follows:

DATEDIFF(date_part, date1, date2, [start_of_week])

For example, DATEDATEDIFF(‘week’, #2019-12-15#, #2019-12-17#, ‘monday’) would return 1.

DATENAME

This function returns the date part of the date in string form. The syntax is as follows:

DATENAME(date_part, date, [start_of_week])

For example, DATENAME(‘month’, #2019-12-17#) would return “December”.

DATEPART

This function returns the date part of the date in integer form. The syntax is as follows:

DATEPART(date_part, date, [start_of_week])

For example, DATEPART(‘month’, #2019-12-17#) would return 12.

DATETRUNC

This function returns the truncated form of the specified date to the accuracy specified by date part. You essentially get returned a new date altogether through this function. The syntax is as follows:

DATETRUNC(date_part, date, [start_of_week])

For example, DATETRUNC(‘quarter’, #2019-12-17#) would return 2019-07-01 12:00:00 AM and DATETRUNC(‘month’, #2019-12-17#) would return 2019-12-01 12:00:00 AM.

DAY

This function returns the day of the given date in integer form. The syntax is as follows:

DAY(Date)

For example, DAY(#2019-12-17#) would return 17.

ISDATE

Given a string is a valid date, this function returns true. The syntax is as follows:

ISDATE(String)

For example, ISDATE(December 17, 2019) would return true.

MAKEDATE

This function returns the date value constructed from the specified year, month, and date. The syntax is as follows:

MAKEDATE(year, month, day)

For example, MAKEDATE(2019, 12, 17) would return #December 17, 2019#.

MAKEDATETIME

This function returns the date and time values constructed from the specified year, month and date, and the hour, minute, and second. The syntax is as follows:

MAKEDATETIME(date, time)

For example, MAKEDATETIME(“2019-12-17”, #11:28:28PM#) would return #12/17/2019 11:28:28 PM#.

DATEPARSE

This function returns the date value parsed from the specified string according to the specified format.

Syntax:

DATEPARSE(format, string)

DATEPARSE(‘yyyy-MM-dd’, ‘2019-12-17’) = #2019-12-17#

DATESERIAL

This function returns the date value constructed from the specified year, month, and date.

Syntax:

DATESERIAL(year, month, day)

DATESERIAL(2019, 12, 17) = #December 17, 2019#

DATESORT

This function returns the specified date in a sortable format.

Syntax:

DATESORT(date)

DATESORT(#2019-12-17#) = 20191217

DATETIME

This function returns the date and time values constructed from the specified year, month, and date and the hour, minute, and second.

Syntax:

DATETIME(year, month, day, hour, minute, second)

DATETIME(2019, 12, 17, 23, 28, 28) = #12/17/2019 11:28:28 PM#

DAYNAME

This function returns the day of the week for the specified date in string form.

Syntax

DAYNAME(date, [start_of_week])

DAYNAME(#2019-12-17#, ‘monday’) = Tuesday

HOUR

This function returns the hour of the specified time.

Syntax:

HOUR(time)

HOUR(#11:28:28 PM#) = 11

MINUTE

This function returns the minute of the specified time.

Syntax:

MINUTE(time)

MINUTE(#11:28:28 PM#) = 28

MONTHNAME

This function returns the month of the specified date in string form.

Syntax:

MONTHNAME(date)

MONTHNAME(#2019-12-17#) = December

SECOND

This function returns the second of the specified time.

Syntax:

SECOND(time)

SECOND(#11:28:28 PM#) = 28

TIME

This function returns the time value constructed from the specified hour, minute, and second.

Syntax:

TIME(hour, minute, second)

TIME(11, 28, 28) = #11:28:28#

YEARISO

This function returns the year of the specified date in ISO 8601 format.

Syntax:

YEARISO(date)

YEARISO(#2019-12-17#) = 2019

These date functions in Tableau can be extremely useful when it comes to working with date fields in your data source. By understanding how these functions work and what they can do, you can make your analysis more efficient and effective.