Date/Time (Format) Functions - trillium_discovery

The following Date/Time (Format) functions are available in the Expression Builder. They do not require conversion to seconds to get results. Rather, date/time must be provided as a string along with an additional parameter that specifies the format of the date/time string. Return values are also strings with the same date/time format. If a date/time value cannot be processed, the function returns a null value.

Note: The Date/Time (Format) function does not support the International Components for Unicode (ICU) Date/Time format.

Some of the Date/Time (Format) functions allows you to specify the "part" of the date/time string to act upon. The value of the "part" is specified through one of the following strings:

Note: These values are case sensitive.

"Year"
"Quarter"
"Month"
"Day"
"Hour"
"Minute"
"Second"
"Millisecond"

The Date/Time (Format) functions are listed below.

Function Name	Description
DT_ADD	Increments (or decrements) part of the date or time and returns a string with the date/time in the same format. Incrementing or decrementing part of the date/time may affect other parts of the dates; for example, incrementing "23:59" by five minutes will affect the day. The part of the date/time being modified does not have to be represented in the format. For example, updating hours for a date. In some cases, if the increment or decrement is not significant, there is no change in the resulting value (for example, if incrementing date by less than 24 hours). Syntax DT_ADD("dt", "format', "part", "increment") where: dt is the date/time to be incremented (or decremented). format is the format of the date (also the return value). part is part of the date that should be incremented (or decremented). increment is the value by which the date is incrementing (or decrementing). Examples DT_ADD("03/12/2013", "dd/MM/yyyy", "Month", "3") = "03/03/2014" DT_ADD("01/03/2014", "dd/MM/yyyy", "Day", "-1") = "28/02/2014" DT_ADD("10:30", "HH:mm", "Minute", "90") = "12:00" DT_ADD("01/03/2014", "dd/MM/yyyy", "Hour", "24") = "02/03/2014"
DT_CEILING	Updates the date/time to the end of the specified part and returns a string with the date/time in the same format. When the date is updated to the end of the specified part, all subsequent parts are also set to their highest value. Syntax DT_CELING("dt", "format", "part") dt is the date/time. format is the format of the date (also of the return value). part is part of the date to be updated (cannot be "Millisecond"). Examples A date/time of 2014-05-08 11:30:23.789 with format yyyy-MM-dd HH:mm:ss.SSS will return the highest value as shown below: If part is "Second", return value is 2014-05-08 11:30:23.999. If part is "Minute", return value is 2014-05-08 11:30:59.999. If part is "Hour", return value is 2014-05-08 11:59:59.999. If part is "Day", return value is 2014-05-08 23:59:59.999. If part is "Month", return value is 2014-05-31 23:59:59.999. If part is "Quarter", return value is 2014-06-30 23:59:59.999. If part is "Year", return value is 2014-12-31 23:59:59.999.
DT_CONVERT	Converts date/time from original format to new specified format and returns a string value. The original date/time must be in a format that has no localized parts. If a localized part (for example "March") is included in the original value and does not match the repository locale,the date/time cannot be parsed. Note: In case a date/time value cannot be parsed, the function returns a null value. Syntax DT_CONVERT("dt", "dt_fmt', "target_fmt") where: dt is the date/time. dt_fmt is the format of the date/time. target_fmt is the format to which the date/time should be converted. Examples DT_CONVERT("03/12/2013", "MM/dd/yyyy", "dd-MM-yy") = "12-03-13"
DT_DIFF	Returns the difference between two date/time values. Difference in seconds is returned as floating number. Difference is negative if the left date/time is less than the right date/time. Syntax DT_DIFF("left", "left_fmt, "right", "right_fmt") left is the date/time on the left hand side. left_fmt is the format of the left hand date/time. right is the date/time on the right hand side. right_fmt is the format of the right hand date/time. Examples DT_DIFF("17:48.22", "HH:mm.ss" "1848", "HHmm") = -3578.0 DT_DIFF("20", "dd", "10", "dd") = 864000.0 DT_DIFF("11.34", "ss.SS", "10:34.44", "mm:ss.SS") = -623.1 DT_DIFF("2004-05-01", "yyyy-MM-dd", "2003-06-01", "yyyy-MM-dd") = 28944000.0
DT_DIFF_PART	Returns the approximate difference between the specified part of two date/time values as an integer. Syntax DT_DIFF_PART("left", "left_fmt', "right", "right_fmt", "part") left is the date/time on the left hand side. left_fmt is the format of the left hand date/time. right is the date/time on the right hand side. right_fmt is the format of the right hand date/time. part is date/time part you are comparing. Examples DT_DIFF_PART("2014-01-05", "yyyy-MM-dd", "2014-04-25", "yyyy-MM-dd", "Month") = 3 DT_DIFF_PART("2014-01-05", "yyyy-MM-dd", "2013-12-05", "yyyy-MM-dd", "Month") = -1 DT_DIFF_PART("0258", "HHmm", "0304", "HHmm", "Hour") = 1 DT_DIFF_PART("0130", "HHmm", "0330", "HHmm", "Minute") = 120
DT_IS_DATE	Evaluates whether the date/time is a date. Returns true if it is a date or false if it is not a date. Syntax DT_IS_DATE("value", "format") value is the date that is evaluated. format is the format of the value. Examples DT_IS_DATE("29/02/2012", "dd/MM/yyyy") = true DT_IS_DATE("31/02/2014", "dd/MM/yyyy") = false DT_IS_DATE("Not a date", "dd/MM/yyyy") = false
DT_IS_DATE_TIME	Evaluates whether the date/time is in the specified format. Returns true if format is same or false if format is different. Syntax DT_IS_DATE_TIME("value", "format") value is the date/time that is evaluated. format is the format of the value. Examples DT_IS_DATE_TIME("Not a date and time", "dd/MM/yyyy HH:mm") = false DT_IS_DATE_TIME("20/12/2013 11:30", "dd/MM/yyyy HH:MM") = true
DT_IS_TIME	Evaluates whether the date/time is time. Returns true if it is a time or false if it is not a time. Syntax DT_IS_TIME("value", "format") value is the time that is evaluated. format is the format of the value. Examples DT_IS_TIME("Not a time", "HH:mm") = false DT_IS_TIME("11:30", "HH:mm") = true
DT_MAX	Compares two date/time values to determine which is later. The format of the later date is used to format the return value. If both values are the same, the return value will have the same format as the left value. Returns a null value if either date/time value cannot be parsed. Syntax DT_MAX("left", "left_fmt", "right", "right_fmt") left is the date/time on the left hand side. left_fmt is the format of the left hand date/time. right is the second date/time to compare on the right hand side. right_fmt is the format of the right hand date/time. Examples DT_MAX("2014-01-02", "yyyy-MM-dd", "2013-01-02", "yyyy-MM-dd") = "2014-01-02" DT_MAX("11:30", "HH:mm", "1130", "HHmm") = 11:30 DT_MAX("11:31", "HH:mm", "ab:cc", "HH:mm") = null
DT_MIN	Compares two date/time values to determine which is earlier. The format of the earlier date is used to format the return value. If both values are the same, the return value will have the same format as the left value. Returns a null value if either date/time value cannot be parsed. Syntax DT_MIN("left", "left_fmt", "right", "right_fmt") left is the date/time on the left hand side. left_fmt is the format of the left hand date/time. right is the second date/time to compare on the right hand side. right_fmt is the format of the right hand date/time. Examples DT_MIN("2014-01-02", "yyyy-MM-dd", "2013-01-02", "yyyy-MM-dd") = "2013-01-02" DT_MIN("11:30", "HH:mm", "1130", "HHmm") = "11:30" DT_MIN("11:31", "HH:mm", "ab:cc", "HH:mm") = null
DT_NAME	Extracts the specified part of the date/time and returns the localized name for it. (The repository locale is used to localize the return value. Therefore, if a repository has a local set to en_GB, it will always return all date/time expressions, even non-British formats in English). If the specified part has no associated name, the extracted value is returned. Syntax DT_NAME("dt", "format", "part") dt is the date/time. format is the format of the date/time. part is the part of dt to extract and name. Examples When locale = en_GB: DT_NAME("2014-06-16", "yyyy-MM-dd", "Day") = "Monday" DT_NAME("2014-06-16", "yyyy-MM-dd", "Month") = "June" DT_NAME("2014-06-16", "yyyy-MM-dd", "Year") = "2014" When locale = fr_FR: DT_NAME("2014-06-16", "yyyy-MM-dd", "Day") = "lundi" DT_NAME("2014-06-16", "yyyy-MM-dd", "Month") = "juin" DT_NAME("2014-06-16", "yyyy-MM-dd", "Year") = "2014"
DT_NOW	Returns the current date/time in the specified format. Syntax DT_NOW("format") format is the format of the date/time. Examples DT_NOW("yyyy-MM-dd HH:mm") = "2014-06-16 16:11" DT_NOW("MM-dd") = "06-16"
DT_PART	Extracts the specified part of the date/time and returns the value as an integer. Syntax DT_PART("dt", "format", "part", "round") dt is the date/time. format is the format of the date/time. part is date/time part you are extracting. Examples DT_PART("04/15/2004", "MM/dd/yyyy", "Year") = 2004 DT_PART("04/15/2004", "MM/dd/yyyy", "Month") = 4
DT_ROUND_DOWN	Rounds down the specified part of a date/time and returns a string with the date/time in same format. The date is changed to the beginning of the given part; all subsequent parts will be set to their lowest values as well. Syntax DT_ROUND_DOWN("dt", "format", "part") dt is the date/time. format is the format of the date/time. part is date/time part to be rounded down. Examples DT_ROUND_DOWN("15/08/2018", "dd/MM/yyyy", "Quarter") = "01/07/2018" DT_ROUND_DOWN("15/02/2018", "dd/MM/yyyy", "Quarter") = "01/01/2018" DT_ROUND_DOWN("15/02/2018", "dd/MM/yyyy", "Month") = "01/02/2018"
DT_ROUND_UP	Rounds up the specified part of a date/time and returns a string with the date/time in same format. Rounding part of a date/time can affect other parts of the date/time. For example: Rounding the year will change the month and day. Rounding to the end of a period will set the date to the beginning of the next period. For example, rounding a date in February to the end of the quarter will set the date to April 1st. When the part is "Millisecond", the milliseconds are rounded to the nearest tenth of a second (for example, 768 milliseconds is rounded to 800 milliseconds, or 0.8 seconds). Otherwise, milliseconds are rounded to the nearest whole second. Syntax DT_ROUND_UP("dt", "format", "part") dt is the date/time. format is the format of the date/time. part is date/time part to be rounded up. Examples DT_ROUND_UP("15/08/2005", "dd/MM/yyyy", "Quarter") = "01/10/2005" DT_ROUND_UP("15/02/2005", "dd/MM/yyyy", "Quarter") = "01/04/2005" DT_ROUND_UP("15/02/2005", "dd/MM/yyyy", "Month") = "01/03/2005"

Date/Time (Format) Functions - trillium_discovery - 17.1

Trillium Expression Builder