Date/Time (Format) Functions
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.
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.
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. 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" |