Date Functions Flashcards
DATEADD
Returns the specified date with the specified number interval added to the specified date_part of that date.
Supports ISO 8601 dates.
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
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.
Supports ISO 8601 dates.
Examples:
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
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.
Supports ISO 8601 dates.
Examples:
DATENAME(‘year’, #2004-04-15#) = “2004”
DATENAME(‘month’, #2004-04-15#) = “April”
DATEPARSE
Returns [date_string] as a date. The date_format argument will describes how the [string] field is arranged. Because of the variety of ways the string field can be ordered, the date_format must match exactly. For a full explanation, see Convert a field to a date field.
Example:
DATEPARSE(‘yyyy-MM-dd’, #2004-04-15#) = “April 4, 2004”
DATEPART
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.
Supports ISO 8601 dates.
Examples:
DATEPART(‘year’, #2004-04-15#) = 2004
DATEPART(‘month’, #2004-04-15#) = 4
DATETRUNC
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.
Supports ISO 8601 dates.
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
Returns the day of the given date as an integer.
Example:
DAY(#2004-04-12#) = 12
ISDATE
Returns true if a given string is a valid date.
Example:
ISDATE(“April 15, 2004”) = true
MAKEDATE
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
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.
Note: This function is available only for MySQL-compatible connections (which for Tableau are MySQL 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
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
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
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
Returns the month of the given date as an integer.
Example:
MONTH(#2004-04-15#) = 4
NOW
Returns the current local system date and time.
Example:
NOW( ) = 2004-04-15 1:08:21 PM
