Date/Time (Format) Functions - trillium_discovery - 17.2

Trillium Expression Builder

Product type
Software
Portfolio
Verify
Product family
Trillium
Product
Trillium > Trillium Quality
Trillium > Trillium Discovery
Version
17.2
Language
English
Product name
Trillium Quality and Discovery
Title
Trillium Expression Builder
Topic type
Administration
Overview
Installation
Reference
How Do I
Configuration
First publish date
2008
ft:lastEdition
2024-07-01
ft:lastPublication
2024-07-01T08:45:29.606589

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).

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.

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.
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.
DT_DIFF_PART

Returns the approximate difference between the specified part of two date/time values as an integer.

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.

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.

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.

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.

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.

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.

DT_NOW

Returns the current date/time in the specified format.

DT_PART

Extracts the specified part of the date/time and returns the value as an integer.

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.

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.