Was this page helpful?
Yes No
Have a comment? Please leave it here. Thanks for your feedback!
All Tableau Help > Tableau Help > 
Design Views and Analyze Data > Advanced Analysis > Calculated Fields > Functions > Date Functions
Applies to: Tableau Desktop, Tableau Online, Tableau Public, Tableau Server

Date Functions

Tableau provides a variety of date functions. Many of the examples use the # symbol with date expressions. See Literal expression syntax for an explanation of this symbol. Additionally, many date functions use date_part, which is a constant string argument. The valid date_part values that you can use are:

date_part Values
'year' Four-digit year
'quarter' 1-4
'month' 1-12 or "January", "February", and so on
'dayofyear' Day of the year; Jan 1 is 1, Feb 1 is 32, and so on
'day' 1-31
'weekday' 1-7 or "Sunday", "Monday", and so on
'week' 1-52
'hour' 0-23
'minute' 0-59
'second' 0-60

Note: Date functions do not take account of the configured fiscal year start. See .Fiscal Dates.

DATEADD(date_part, interval, date)

Returns the specified date with the specified number interval added to the specified date_part of that date.

Example

DATEADD('month', 3, #2004-04-15#) = 2004-07-15 12:00:00 AM

This expression adds three months to the date #2004-04-15#.

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

Returns the difference between date1 and date2 expressed in units of date_part.

The start_of_week parameter, which you can use to specify which day is to be considered the first day or the week, is optional. Possible values are 'monday', 'tuesday', etc. If it is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.

Example

DATEDIFF('week', #2013-09-22#, #2013-09-24#, 'monday')= 1

DATEDIFF('week', #2013-09-22#, #2013-09-24#, 'sunday')= 0

The first expression returns 1 because when start_of_week is 'monday', then 22 September (a Sunday) and 24 September (a Tuesday) are in different weeks. The second expression returns 0 because when start_of_week is 'sunday' then 22 September (a Sunday) and 24 September (a Tuesday) are in the same week.

DATENAME(date_part, date, [start_of_week])

Returns date_part of date as a string. The start_of_week parameter, which you can use to specify which day is to be considered the first day or the week, is optional. Possible values are 'monday', 'tuesday', etc. If start_of_week is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.

Examples

DATENAME('year', #2004-04-15#) = "2004"

DATENAME('month', #2004-04-15#) = "April"

DATEPART(date_part, date, [start_of_week])

Returns date_part of date as an integer.

The start_of_week parameter, which you can use to specify which day is to be considered the first day or the week, is optional. Possible values are 'monday', 'tuesday', etc. If start_of_week is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.

Note: When the date_part is weekday, the start_of_week parameter is ignored. This is because Tableau relies on a fixed weekday ordering to apply offsets.

Examples

DATEPART('year', #2004-04-15#) = 2004

DATEPART('month', #2004-04-15#) = 4

DATETRUNC(date_part, date, [start_of_week])

Truncates the specified 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. The start_of_week parameter, which you can use to specify which day is to be considered the first day or the week, is optional. Possible values are 'monday', 'tuesday', etc. If start_of_week is omitted, the start of week is determined by the data source. See Date Properties for a Data Source.

Examples

DATETRUNC('quarter', #2004-08-15#) = 2004-07-01 12:00:00 AM

DATETRUNC('month', #2004-04-15#) = 2004-04-01 12:00:00 AM

DAY(date)

Returns the day of the given date as an integer.

Example

DAY(#2004-04-12#) = 12

ISDATE(string)

Returns true if a given string is a valid date.

Example

ISDATE("April 15, 2004") = true

MAKEDATE(year, month, day)

Returns a date value constructed from the specified year, month, and date.

Available for Tableau Data Extracts. Check for availability in other data sources.

Example

MAKEDATE(2004, 4, 15) = #April 15, 2004#

MAKEDATETIME(date, time)

Returns a datetime that combines a date and a time. The date can be a date, datetime, or a string type. The time must be a datetime. This function is available only for MySQL-compatible connections (which for Tableau are, in addition to MySQL, Amazon Aurora and Amazon Aurora).

Examples

MAKEDATETIME("1899-12-30", #07:59:00#) = #12/30/1899 7:59:00 AM#

MAKEDATETIME([Date], [Time]) = #1/1/2001 6:00:00 AM#

MAKETIME(hour, minute, second)

Returns a date value constructed from the specified hour, minute, and second.

Available for Tableau Data Extracts. Check for availability in other data sources.

Example

MAKETIME(14, 52, 40) = #14:52:40#

MAX(expression) or MAX(expr1, expr2)

Usually applied to numbers but also works on dates. Returns the maximum of a and b (a and b must be of the same type). Returns Null if either argument is Null.

Examples

MAX(#2004-01-01# ,#2004-03-01#) = 2004-03-01 12:00:00 AM

MAX([ShipDate1], [ShipDate2])

MIN(expression) or MIN(expr1, expr2)

Usually applied to numbers but also works on dates. Returns the minimum of a and b (a and b must be of the same type). Returns Null if either argument is Null.

Examples

MIN(#2004-01-01# ,#2004-03-01#) = 2004-01-01 12:00:00 AM

MIN([ShipDate1], [ShipDate2])

MONTH(date)

Returns the month of the given date as an integer.

Example

MONTH(#2004-04-15#) = 4

NOW( )

Returns the current date and time.

The return varies depending on the nature of the connection:

Example

NOW( ) = 2004-04-15 1:08:21 PM

TODAY( )

Returns the current date.

Example

TODAY( ) = 2004-04-15

YEAR (date)

Returns the year of the given date as an integer.

Example

YEAR(#2004-04-15#) = 2004