Available through the ExpressionConfiguration.StandardFunctionsDictionary constant:
Name Description ABS Absolute (non-negative) value AVERAGE Returns the average (arithmetic mean) of all parameters. If a parameter is of type ARRAY , the average of all elements is calculated. CEILING Rounds the given value to the nearest integer using the rounding mode CEILING COALESCE Returns the first non-null parameter, or NULL if all parameters are null. If a parameter is of type ARRAY, returns the first non-null element. FACT Calculates the factorial of a base value FLOOR Rounds the given value to the nearest integer using the rounding mode FLOOR IF Conditional evaluation function. Returns one value or another, depending on a given condition. LOG The natural logarithm (base e) of a value LOG10 The base 10 logarithm of a value MAX Returns the maximum value of all parameters. If a parameter is of type ARRAY , the maximum of all elements is calculated. MIN Returns the minimum value of all parameters. If a parameter is of type ARRAY , the minimum of all elements is calculated. NOT Boolean negation, implemented as a function (for compatibility) RANDOM Produces a random value between 0 and 1 ROUND Rounds the given value to the specified scale, using the current rounding mode SQRT Returns the square root of a given number SUM Returns the sum of all parameters. If a parameter is of type ARRAY , the sum of all elements is calculated. SWITCH Returns the result correponding to the first matching value in the specified expression or an optional default value if no match found.
Name Description STR_CONTAINS Returns true if the string contains the substring (case-insensitive) STR_ENDS_WITH Returns true if the string ends with the substring (case-sensitive) STR_FORMAT Returns a formatted string using the specified format string and arguments, using the configured locale STR_LEFT Returns the first n characters from the left of the given string STR_LENGTH Returns the length of the string STR_LOWER Converts the given value to lower case STR_MATCHES Returns true if the string matches the RegEx pattern STR_RIGHT Returns the last n characters from the left of the given string STR_SPLIT Splits the given string into an array, given a separator STR_STARTS_WITH Returns true if the string starts with the substring (case-sensitive) STR_SUBSTRING Returns a portion of the string from the specified start index up to the end index (or to the end of the string if not specified) STR_TRIM Returns the given string with all leading and trailing spaces removed STR_UPPER Converts the given value to upper case
Name Description ACOS(value) Returns the arc-cosine (in degrees) ACOSH(value) Returns the hyperbolic arc-cosine ACOSR(value) Returns the arc-cosine (in radians) ACOT(value) Returns the arc-co-tangent (in degrees) ACOTH(value) Returns the hyperbolic arc-co-tangent (in degrees) ACOTR(value) Returns the arc-co-tangent (in radians) ASIN(value) Returns the arc-sine (in degrees) ASINH(value) Returns the hyperbolic arc-sine ASINR(value) Returns the arc-sine (in radians) ATAN2(y, x) Returns the angle of atan2 (in degrees) ATAN2R(y, x) Returns the angle of atan2 (in radians) ATAN(value) Returns the arc-tangent (in degrees) ATANH(value) Returns the hyperbolic arc-tangent ATANR(value) Returns the arc-tangent (in radians) COS(value) Returns the cosine of an angle (in degrees) COSH(value) Returns the hyperbolic cosine of a value COSR(value) Returns the cosine of an angle (in radians) COT(value) Returns the co-tangent of an angle (in degrees) COTH(value) Returns the hyperbolic co-tangent of a value COTR(value) Returns the co-tangent of an angle (in radians) CSC(value) Returns the co-secant of an angle (in degrees) CSCH(value) Returns the hyperbolic co-secant of a value CSCR(value) Returns the co-secant of an angle (in radians) DEG(rad) Converts an angle measured in radians to an approximately equivalent angle measured in degrees RAD(degrees) Converts an angle measured in degrees to an approximately equivalent angle measured in radians SEC(value) Returns the secant of an angle (in degrees) SECH(value) Returns the hyperbolic secant of an angle SECR(value) Returns the secant of an angle (in radians) SIN(value) Returns the sine of an angle (in degrees) SINH(value) Returns the hyperbolic sine of a value SINR(value) Returns the sine of an angle (in radians) TAN(value) Returns the tangent of an angle (in degrees) TANH(value) Returns the hyperbolic tangent of a value TANR(value) Returns the tangent of an angle (in radians)
Name Description DT_DATE_NEW Returns a new DATE_TIME value with the given parameters (year, month, day, etc.). An optional time zone (string) can be specified, e.g. “Europe/Berlin”, or “GMT+02:00”. If no zone ID is specified, the configured zone ID is used. DT_DATE_NEW(millis) Returns a new DATE_TIME given the number of milliseconds from the Unix epoch (1970-01-01T00:00:00Z). DT_DATE_PARSE Converts the given string into a DATE_TIME value by using the optional time zone and formats. The formats are applied until the first match. Without a format, the configured formats are used. Time zone can be NULL, then the configured time zone and locale are used. DT_DATE_FORMAT Formats the given DATE_TIME into a string using the given optional format and time zone. Without a format, the first configured format is used. The zone ID defaults to the configured zone ID. DT_DATE_TO_EPOCH Converts the given DATE_TIME value into epoch timestamp in millisecond. DT_DURATION_NEW Returns a new DURATION value with the given parameters. DT_DURATION_PARSE Converts the given ISO-8601 duration string representation into a DURATION value. E.g. “P2DT3H4M” parses 2 days, 3 hours and 4 minutes. DT_DURATION_FROM_MILLIS Returns a new DURATION value with the given milliseconds. DT_DURATION_TO_MILLIS Converts the given DURATION to a milliseconds value. DT_NOW Produces a new DATE_TIME that represents the current moment in time. DT_TODAY Produces a new DATE_TIME that represents the current date, at midnight (00:00). An optional time zone can be specified, e.g. “America/Sao_Paulo”, or “GMT-03:00”. If no zone ID is specified, the configured zone ID is used.
The ABS function returns the absolute (non-negative) value of a given number.
Name Description value The number from which to obtain the absolute value.
Consider the following variables:
And the following expressions:
Expression Result ABS(x)5 ABS(y)10
Since: 3.3.0
The AVERAGE function calculates the arithmetic mean of a set of numbers. If any parameter is an array, the average of all its elements is calculated.
Name Description value, … One or more values or arrays from which the average is calculated.
Consider the following variables:
Name Value scores[70, 80, 90] a85 b95
And the following expressions:
Expression Result AVERAGE(scores)80 AVERAGE(a, b)90
The CEILING function rounds a given number up to the nearest integer using the rounding mode CEILING.
See Rounding Modes for more information.
Name Description value The number to be rounded up to the nearest integer.
Consider the following expressions:
Expression Result CEILING(4.3)5 CEILING(2.7)-2
Since: 3.1.0
The COALESCE function returns the first non-null parameter from a list of arguments, or NULL if all parameters are null.
Name Description value, … One or more values or arrays from which to be evaluated.
Consider the following variables:
Name Value anull b"hello" c42 numbers[null, 8, 5]
And the following expressions:
Expression Result COALESCE(a, b, c)"hello" COALESCE(a, null, c)42 COALESCE(a, null)null COALESCE(numbers)8 COALESCE(a, numbers, b)8
The FACT function calculates the factorial of a base value, that is, the product of all positive integers from 1 up to that number.
Name Description base The number for which the factorial is calculated.
Consider the following expressions:
Expression Result FACT(5)120 FACT(3)6
The FLOOR function rounds a given number down to the nearest integer using the rounding mode FLOOR.
See Rounding Modes for more information.
Name Description value The number to be rounded down to the nearest integer.
Consider the following expressions:
Expression Result FLOOR(4.7)4 FLOOR(2.4)-3
The IF function performs a logical test and returns one value if the test is true and another one if it’s false.
IF(condition, value_if_true, value_if_false)
Name Description condition The logical test you want to evaluate. value_if_true The value returned if the logical test is true. value_if_false The value returned if the logical test is false.
Consider the following variables:
Name Value myFlagfalse numSales1200 status"a"
And the following expressions:
Expression Result IF(myFlag, "On", "Off")"Off" IF(numSales > 1000, "High", "Low")"High" IF(status == "a", "Active", "Suspended")"Active"
The LOG function returns the natural logarithm (base e) of a given value.
Name Description value The number for which to calculate the natural logarithm.
Consider the following expressions:
Expression Result LOG(2.718)1 LOG(1)0 LOG(10)2.302585092994046
The LOG10 function returns the base 10 logarithm of a given value.
Name Description value The number for which to calculate the base 10 logarithm.
Consider the following expressions:
Expression Result LOG10(100)2 LOG10(10)1 LOG10(2.12345)0.3270420392943239
The MAX function returns the maximum value of all parameters. If any parameter is an array, the maximum of all its elements is calculated.
Name Description value, … One or more numbers or arrays from which to find the maximum value.
Consider the following variables:
Name Value numbers[3, 8, 5] x7 y4
And the following expressions:
Expression Result MAX(numbers)8 MAX(x, y)7 MAX(x, y, numbers)8
The MIN function returns the minimum value of all parameters. If any parameter is an array, the minimum of all its elements is calculated.
Name Description value, … One or more numbers or arrays from which to find the minimum value.
Consider the following variables:
Name Value numbers[3, 8, 5] x7 y4
And the following expressions:
Expression Result MIN(numbers)3 MIN(x, y)4 MIN(x, y, numbers)3
The NOT function performs a Boolean negation on a given value.
Name Description value The boolean value to be negated.
Consider the following variables:
Name Value flagtrue x10
And the following expressions:
Expression Result NOT(flag)false NOT(x < 5)true
The RANDOM function produces a random value between 0 and 1.
Expressions that use RANDOM will produce varying results:
Expression Result RANDOM()0.372 (example) RANDOM()0.847 (example)
The ROUND function rounds a given value to the specified scale using the current rounding mode.
See Rounding Modes for more information.
Name Description value The number to be rounded. scale The number of decimal places to round to.
Consider the following variables:
And the following expressions:
Expression Result ROUND(num, 1)5.7 ROUND(num, 2)5.68
The SQRT function calculates the square root of a given number.
Note: This function uses the implementation from The Java Programmers Guide To numerical Computing by Ronald Mak, 2002.
Name Description value The number for which to calculate the square root.
Consider the following expressions:
Expression Result SQRT(16)4 SQRT(9)3
The SUM function calculates the total of a set of numbers. If any parameter is an array, the sum of all its elements is calculated.
Name Description value, […] One or more values or arrays from which to calculate the sum.
Consider the following variables:
Name Value values[3, 8, 5] a10 b15
And the following expressions:
Expression Result SUM(values)16 SUM(a, b)25 SUM(a, b, values)41
Since: 3.3.0
The SWITCH function provides a way to compare one value against a series of possible values and return a specific result based on the first matching value. It’s similar to nested IF statements but can be more concise and easier to read for certain scenarios.
SWITCH(expression, value1, result1, [value2-N, result2-N, ...], [default])
Name Description expression The value or expression that the function will compare against the provided list of values. value1, result1, value2, result2, … Pairs of values, where the function checks if the expression matches the first value in each pair. default An optional default value to be returned if no matching value is found.
Consider the following variables:
Name Value weekday1 country_code"BRA" status"x"
And the following expressions:
Expression Result SWITCH(weekday, 1, "Sunday", 2, "Monday", 3, "Tuesday")"Sunday" SWITCH(country_code, "DEU", "Germany", "BRA", "Brazil")"Brazil" SWITCH(status, "a", "Active", "s", "Suspended", "Unknown")"Unknown"
The STR_CONTAINS function checks whether a specified substring is present within a larger string. This comparison is case-insensitive , meaning it does not differentiate between uppercase and lowercase letters.
STR_CONTAINS(string, substring)
Name Description string The text in which you want to search for the presence of a substring. substring The text to search for within the main string.
Consider the following variables:
Name Value title"Introduction" phrase"quick brown fox"
And the following expressions:
Expression Result STR_CONTAINS(title, "intro")true STR_CONTAINS(phrase, "BROWN")true STR_CONTAINS(phrase, "lazy")false
Since: 3.2.0
The STR_ENDS_WITH function checks if a string ends with the specified substring. This comparison is case-sensitive .
STR_ENDS_WITH(string, substring)
Name Description string The text to be evaluated. substring The text to check for at the end of the main string.
Consider the following variables:
Name Value file"Report.pdf" word"sunflower"
And the following expressions:
Expression Result STR_ENDS_WITH(file, ".pdf")true STR_ENDS_WITH(word, "flower")true STR_ENDS_WITH(word, "Flower")false
Since: 3.3.0
The STR_FORMAT function returns a formatted string using the provided format string and one or more arguments.
The format respects the configured locale for number and date presentation.
Learn how to change the locale in the Configuration section.
STR_FORMAT(format[, argument, ...])
Name Description format A string format in the Java Formatter syntax . arguments One or more arguments referenced by the format specifiers in the format string.
Consider the following variables:
Name Value name"Maria" count3 price25.5
And the following expressions:
Expression Locale Result STR_FORMAT("Hello, %s!", name)English "Hello, Maria!" STR_FORMAT("%03.0f items", count)English "003 items" STR_FORMAT("Total: %.2f", price)English "Total: 25.50" STR_FORMAT("Total: %.2f", price)German "Total: 25,50"
Since: 3.4.0
The STR_LEFT function extracts the first n characters from the start (left side) of a given string.
Name Description value The original string from which to extract text. n The number of characters to return from the left.
Consider the following variables:
Name Value city"Barcelona" language"Spanish"
And the following expressions:
Expression Result STR_LEFT(city, 3)"Bar" STR_LEFT(language, 16)"Spanish"
Since: 3.4.0
The STR_LENGTH function returns the total number of characters in a string, including spaces and punctuation.
Name Description string The text whose length you want to measure.
Consider the following expressions:
Expression Result STR_LENGTH("abc")3 STR_LENGTH("Hello, EvalEx!")14
The STR_LOWER function converts all characters in a given string to lowercase.
Name Description value The text to be converted.
Consider the following expressions:
Expression Result STR_LOWER("BR")"br" STR_LOWER("ABC1-abc2")"abc1-abc2"
Since: 3.4.0
The STR_MATCHES function checks whether the given string matches a specific regular expression pattern .
STR_MATCHES(string, pattern)
Name Description string The text to test against the regex pattern. pattern A regular expression used for matching.
Name Value message"Hello World” email"test@abc.com" zip"12345-678"
Expression Result STR_MATCHES(message, ".*World")true STR_MATCHES(email, "\\S+@\\S+\\.\\S+")true STR_MATCHES(zip, "^\\d{5}-\\d{3}$")true STR_MATCHES(zip, "^\\d{8}$")false
Since: 3.4.0
The STR_RIGHT function extracts the last n characters from a string.
Name Description value The original string to extract from. n The number of characters to return from the end.
Consider the following variables:
Name Value message"Hello World" code"HTTP/404"
And the following expressions:
Expression Result STR_RIGHT(message, 5)"World" STR_RIGHT(code, 3)"404"
Since: 3.5.0
The STR_SPLIT function splits a string into an array of substrings based on the provided separator.
STR_SPLIT(string, separator)
Name Description string The original text to split. separator A delimiter string used to divide the input string.
Consider the following variables:
Name Value colors"red,green,blue" path"home/user/documents"
And the following expressions:
Expression Result STR_SPLIT(colors, ",")["red", "green", "blue"] STR_SPLIT(path, "/")["home", "user", "documents"]
Since: 3.2.0
The STR_STARTS_WITH function checks whether a string begins with the specified substring. This comparison is case-sensitive .
STR_STARTS_WITH(string, substring)
Name Description string The main text you want to examine. substring The expected prefix to test at the beginning of string.
Consider the following variables:
Name Value title"DataSet" code"ERR-2025"
And the following expressions:
Expression Result STR_STARTS_WITH(title, "Data")true STR_STARTS_WITH(title, "data")false STR_STARTS_WITH(code, "ERR")true
Since: 3.4.0
The STR_SUBSTRING function extracts a portion of a string starting from a given index and optionally ending at another index. If the end is not specified, it returns the substring until the end of the string.
STR_SUBSTRING(string, start[, end])
Name Description string The original string from which to extract a section. start The starting index (inclusive). Indexing begins at 0. end (Optional) The ending index (exclusive). Defaults to the end of the string.
Consider the following variables:
Name Value text"Expression" id"AB123456"
And the following expressions:
Expression Result STR_SUBSTRING(text, 0, 4)"Expr" STR_SUBSTRING(text, 5)"ssion" STR_SUBSTRING(id, 2, 6)"1234"
Since: 3.3.0
The STR_TRIM function removes all leading and trailing whitespace from a given string.
Name Description string The text to be trimmed of whitespace.
Consider the following variables:
Name Value input1" Hello world " input2"\tTabbed text\t"
And the following expressions:
Expression Result STR_TRIM(input1)"Hello world" STR_TRIM(input2)"Tabbed text"
The STR_UPPER function converts all characters in a given string to uppercase.
Name Description value The text to be converted.
Consider the following expressions:
Expression Result STR_UPPER("abc")"ABC" STR_UPPER("EvalEx")"EVALEX"
Returns the arc-cosine of a value, in degrees.
Name Description value A numeric value between -1 and 1.
Expression Result ACOS(-1)180 ACOS(0)90 ACOS(0.5)60 ACOS(1)0
Returns the hyperbolic arc-cosine of a value.
Name Description value A numeric value, greather than or equal to 1.
Expression Result ACOSH(1)0 ACOSH(2)1.3169578969248166 ACOSH(3)1.762747174039086
Returns the arc-cosine of a value, in radians.
Name Description value A numeric value between -1 and 1.
Expression Result ACOSR(-1)3.141592653589793 ACOSR(0)1.5707963267948966 ACOSR(1)0
Returns the arc-co-tangent of a value, in degrees.
Name Description value A numeric value.
Expression Result ACOT(-1)135 ACOT(1)45
Returns the hyperbolic arc-co-tangent of a value, in degrees.
Name Description value A numeric value > 1 or < -1.
Expression Result ACOTH(-1.5)-0.8047189562170501 ACOTH(1.5)0.8047189562170501
Returns the arc-co-tangent of a value, in radians.
Name Description value A numeric value.
Expression Result ACOTR(1)0.7853981633974483 ACOTR(-1)2.356194490192345
Returns the arc-sine of a value, in degrees.
Name Description value A numeric value between -1 and 1.
Expression Result ASIN(0)0 ASIN(0.5)30 ASIN(1)90 ASIN(-1)-90
Returns the hyperbolic arc-sine of a value.
Name Description value A numeric value.
Expression Result ASINH(0)0 ASINH(1)0.8813735870195429 ASINH(-1)-0.8813735870195428
Returns the arc-sine of a value, in radians.
Name Description value A numeric value between -1 and 1.
Expression Result ASINR(0)0 ASINR(1)1.5707963267948966 ASINR(-1)-1.5707963267948966
Returns the angle of atan2, in degrees.
Name Description y The ordinate (vertical) value. x The abscissa (horizontal) value.
Expression Result ATAN2(0, 0)0 ATAN2(0, 1)0 ATAN2(0, -1)180 ATAN2(1, 0)90 ATAN2(1, -1)135 ATAN2(-1, 0)-90 ATAN2(-1, 1)-45 ATAN2(-1, -1)-135
Returns the angle of atan2, in radians.
Name Description y The ordinate (vertical) value. x The abscissa (horizontal) value.
Expression Result ATAN2R(0, 0)0 ATAN2R(0, 1)0 ATAN2R(0, -1)3.141592653589793 ATAN2R(1, 0)1.5707963267948966 ATAN2R(1, 1)0.7853981633974483 ATAN2R(1, -1)2.356194490192345 ATAN2R(-1, 0)-1.5707963267948966 ATAN2R(-1, -1)-2.356194490192345
Returns the arc-tangent of a value, in degrees.
Name Description value A numeric value.
Expression Result ATAN(0)0 ATAN(1)45 ATAN(-1)-45
Returns the hyperbolic arc-tangent of a value.
Name Description value A numeric value between -1 and 1.
Expression Result ATANH(0)0 ATANH(0.9)1.4722194895832204 ATANH(-0.9)-1.4722194895832204
Returns the arc-tangent of a value, in radians.
Name Description value A numeric value.
Expression Result ATANR(0)0 ATANR(1)0.7853981633974483 ATANR(-1)-0.7853981633974483
Returns the cosine of an angle, in degrees.
Name Description value Angle in degrees.
Expression Result COS(0)1 COS(60)0.5
Returns the hyperbolic cosine of a value.
Name Description value A numeric value.
Expression Result COSH(0)1 COSH(1)1.543080634815244 COSH(-1)1.543080634815244
Returns the cosine of an angle, in radians.
Name Description value Angle in radians.
Expression Result COSR(0)1 COSR(1)0.5403023058681398 COSR(1.5708)0
Returns the co-tangent of an angle, in degrees.
Name Description value Angle in degrees.
Expression Result COT(45)1 COT(30)1.7320508075688774
Returns the hyperbolic co-tangent of a value.
Name Description value A numeric value not equal to 0.
Expression Result COTH(1)1.3130352854993315 COTH(5)1.0000908039820193 COTH(-5)-1.0000908039820193
Returns the co-tangent of an angle, in radians.
Name Description value Angle in radians (not zero).
Expression Result COTR(0.7854)1 COTR(1)0.6420926159343306
Returns the co-secant of an angle, in degrees.
Name Description value Angle in degrees (not zero).
Expression Result CSC(30)2 CSC(90)1
Returns the hyperbolic co-secant of a value.
Name Description value A numeric value not equal to 0.
Expression Result CSCH(1)0.8509181282393216 CSCH(-1)-0.8509181282393216
Returns the co-secant of an angle, in radians.
Name Description value Angle in radians (not zero).
Expression Result CSCR(0.5236)2 CSCR(1.5708)1
Converts an angle measured in radians to an approximately equivalent angle measured in degrees.
Name Description rad Angle in radians.
Expression Result DEG(0)0 DEG(1.5708)90 DEG(3.1416)180
Converts an angle measured in degrees to an approximately equivalent angle measured in radians.
Name Description degrees Angle in degrees.
Expression Result RAD(0)0 RAD(90)1.5708 RAD(180)3.1416
Returns the secant of an angle, in degrees.
Name Description value Angle in degrees (not zero).
Expression Result SEC(60)2 SEC(0)1
Returns the hyperbolic secant of a value.
Name Description value A numeric value (not zero).
Expression Result SECH(1)0.6480542736638853 SECH(0)1
Returns the secant of an angle, in radians.
Name Description value Angle in radians (not zero).
Expression Result SECR(1.0472)2 SECR(0)1
Returns the sine of an angle, in degrees.
Name Description value Angle in degrees.
Expression Result SIN(0)0 SIN(30)0.5 SIN(90)1
Returns the hyperbolic sine of a value.
Name Description value A numeric value.
Expression Result SINH(0)0 SINH(1)1.1752011936438014 SINH(-1)-1.1752011936438014
Returns the sine of an angle, in radians.
Name Description value Angle in radians.
Expression Result SINR(0)0 SINR(0.5236)0.5 SINR(1.5708)1
Returns the tangent of an angle, in degrees.
Name Description value Angle in degrees.
Expression Result TAN(0)0 TAN(45)1 TAN(30)0.5773502691896257
Returns the hyperbolic tangent of a value.
Name Description value A numeric value.
Expression Result TANH(0)0 TANH(1)0.7615941559557649 TANH(-1)-0.7615941559557649
Returns the tangent of an angle, in radians.
Name Description value Angle in radians.
Expression Result TANR(0)0 TANR(0.7854)1 TANR(1)1.5574077246549023
The DT_DATE_NEW function creates a new DATE_TIME value using the specified date components, with optional time, sub-second precision, and time zone. If no time zone is provided, the configured system default is used.
DT_DATE_NEW(year, month, day [, hour] [, minute] [, second] [, nanos] [, zoneId])
Name Description year The year (e.g. 2025). month The month number (1 = January, 12 = December). day The day of the month. hour (Optional) Hour of the day (0–23). minute (Optional) Minute of the hour (0–59). second (Optional) Second of the minute (0–59). nanos (Optional) Nanoseconds (0–999,999,999). zoneId (Optional) Time zone identifier as a string (e.g. "Europe/Berlin", or "GMT+02:00").
These examples illustrate how different combinations of date, time, and zone components produce distinct DATE_TIME results.
Expression Result (example) DT_DATE_NEW(2025, 6, 15)2025-06-15T00:00:00Z* DT_DATE_NEW(2025, 6, 15, 9, 30)2025-06-15T09:30:00Z* DT_DATE_NEW(2025, 6, 15, "Europe/Berlin")2025-06-15T00:00:00+02:00[Europe/Berlin] DT_DATE_NEW(2025, 6, 15, 9, 30, 0, 0, "Europe/Berlin")2025-06-15T09:30:00+02:00[Europe/Berlin]
* Timezone and precision may vary depending on your system configuration.
The DT_DATE_NEW function can also create a DATE_TIME value by specifying a number of milliseconds since the Unix epoch (1970-01-01T00:00:00Z).
Name Description millis The number of milliseconds since 1970-01-01T00:00:00Z. Must be a positive NUMBER.
Use this form when working with timestamp values or durations that represent time since the epoch.
Expression Result (example)* DT_DATE_NEW(0)1970-01-01T00:00:00Z DT_DATE_NEW(1489227300000)2017-03-11T10:15:00Z
* Examples considering UTC as system time zone.
The DT_DATE_PARSE function converts a string into a DATE_TIME value by attempting to match it against one or more date-time formats until success. Optionally, a specific time zone may also be provided. If no format is supplied, the system’s configured date/time formats will be used. If the zone is omitted or NULL, the system’s default zone and locale apply.
DT_DATE_PARSE(value [, zoneId] [, format, ...])
Name Description value The text to be parsed into a date/time. Must be of type STRING. zoneId (Optional) A time zone identifier string (e.g. "Europe/Berlin", "UTC", or "GMT-03:00"). format (Optional) One or more custom date-time format strings (e.g. "yyyy-MM-dd HH:mm", "dd/MM/yyyy").
These examples demonstrate how different formats and zone configurations influence how the date string is interpreted.
Expression Result (example) DT_DATE_PARSE("2025-06-16T17:41:00Z")2025-06-16T17:41:00Z DT_DATE_PARSE("16/06/2025 17:41", "Europe/Lisbon", "dd/MM/yyyy HH:mm")2025-06-16T17:41:00+01:00[Europe/Lisbon] DT_DATE_PARSE("06-16-2025", "MM-dd-yyyy")2025-06-16T00:00:00Z (system zone)
The DT_DATE_FORMAT function formats a given DATE_TIME value into a string. You can optionally supply a specific format pattern and time zone. If omitted, the system’s configured default format and time zone are used.
DT_DATE_FORMAT(value [, format] [, zoneId])
Name Description value The DATE_TIME value to format. format (Optional) Format string following date/time pattern syntax (e.g. "yyyy-MM-dd HH:mm:ss"). zoneId (Optional) Time zone identifier string to use (e.g. "UTC", "America/Chicago").
These examples show how various combinations of formats and zones affect the resulting string.
Note: The function DT_DATE_NEW is being used to produce DATE_TIME values for the format operations.
Expression Result (example)* DT_DATE_FORMAT(DT_DATE_NEW(2019, 6, 12, 18, 10))"2019-06-12T18:10:00Z" DT_DATE_FORMAT(DT_DATE_NEW(2019, 6, 12, 21, 10), "dd/MM/yyyy HH:mm", "America/Sao_Paulo")"12/06/2019 18:10" DT_DATE_FORMAT(DT_DATE_NEW(2019, 6, 12), "EEEE, MMM d, yyyy")"Wednesday, Jun 12, 2019"
* Examples considering UTC as system time zone.
The DT_DATE_TO_EPOCH function converts a DATE_TIME value to the number of milliseconds elapsed since the Unix epoch (1970-01-01T00:00:00Z).
Name Description value A DATE_TIME value to convert into epoch millis.
These examples demonstrate how this function turns time values into numeric timestamps.
Note: The function DT_DATE_NEW is being used to produce DATE_TIME values for the operations.
Expression Result (example)* DT_DATE_TO_EPOCH(DT_DATE_NEW(1970, 1, 1))0 DT_DATE_TO_EPOCH(DT_DATE_NEW(2022, 3, 19, 10, 15))1647684900000 DT_DATE_TO_EPOCH(DT_DATE_NEW(1969, 12, 31, 23, 59))-60000
* Examples considering UTC as system time zone.
The DT_DURATION_NEW function constructs a DURATION value using the specified number of days, with optional time-based components such as hours, minutes, seconds, and nanoseconds.
DT_DURATION_NEW(days [, hours] [, minutes] [, seconds] [, millis] [, nanos])
Name Description days The number of days. Required. hours (Optional) Number of hours to include. minutes (Optional) Number of minutes to include. seconds (Optional) Number of seconds to include. millis (Optional) Milliseconds (1/1,000 of a second). nanos (Optional) Nanoseconds (1/1,000,000,000 of a second).
Note: Although both millis and nanos are accepted, it’s generally recommended to use either one or the other to represent sub-second precision. If you provide nanoseconds, it should capture the full fractional portion, and millis should be set to 0 to avoid double counting.
These examples show how duration values can range from whole days to finely tuned time spans.
Expression Result DT_DURATION_NEW(5)PT120H DT_DURATION_NEW(0, 1, 15)PT1H15M DT_DURATION_NEW(1, 0, 0, 30)PT24H30S DT_DURATION_NEW(0, 0, 0, 0, 999)PT0.999S DT_DURATION_NEW(0, 0, 0, 0, 0, 999999999)PT0.999999999S
The DT_DURATION_PARSE function converts an ISO-8601 compliant duration string into a DURATION value. This format can represent combinations of days, hours, minutes, seconds, and more using a standardized pattern.
Name Description value An ISO-8601 duration string (e.g. "P2DT3H4M" for 2 days, 3 hours, and 4 minutes).
These examples highlight how standard duration strings are interpreted and parsed into precise DURATION values.
Expression Result DT_DURATION_PARSE("P1D")PT24H DT_DURATION_PARSE("PT2H30M")PT2H30M DT_DURATION_PARSE("P2DT3H4M")PT51H4M DT_DURATION_PARSE("PT0.75S")PT0.75S
The DT_DURATION_FROM_MILLIS function creates a DURATION value from the number of milliseconds provided. This is useful when handling timestamp or elapsed-time data that’s already in millisecond precision.
DT_DURATION_FROM_MILLIS(millis)
Name Description millis The number of milliseconds as a NUMBER value.
These examples convert raw millisecond values into proper DURATION values.
Expression Result DT_DURATION_FROM_MILLIS(60000)PT1M DT_DURATION_FROM_MILLIS(0)PT0S DT_DURATION_FROM_MILLIS(1234)PT1.234S
The DT_DURATION_TO_MILLIS function returns the number of milliseconds in a given DURATION value. It’s commonly used to serialize durations into numeric formats for storage or computation.
DT_DURATION_TO_MILLIS(duration)
Name Description duration A DURATION value to be converted to millis.
Here are some sample conversions showing how durations get flattened into exact millisecond quantities.
Note: The function DT_DURATION_PARSE is being used to produce DURATION values for the operations.
Expression Result DT_DURATION_TO_MILLIS(DT_DURATION_PARSE("PT1S"))1000 DT_DURATION_TO_MILLIS(DT_DURATION_PARSE("PT1M"))60000
The DT_NOW function returns a DATE_TIME value representing the exact current instant, perfect for logging, comparisons, or triggering time-based events.
Consider the following variable:
Name Value dueDate2025-05-31T23:59:59.999Z (DATETIME)
Here are some expressions. The first one produces a DATE_TIME value. The second one uses the result to compare against another DATE_TIME using the IF function.
Expression Result (example) DT_NOW()2025-06-25T10:47:26.234Z* IF(dueDate < DT_NOW(), "due", "overdue")"overdue"
* Example considering UTC as system time zone
The DT_TODAY function returns a DATE_TIME representing the current date with time set to midnight (00:00). You may optionally provide a time zone; otherwise, the system’s configured zone is used.
Name Description zoneId (Optional) Time zone identifier string (e.g. "Africa/Nairobi", or "GMT+03:00")
Use this when you need a reference to today’s date without the current time component.
Expression Result (example) DT_TODAY()2025-06-25T00:00:00Z* DT_TODAY("Australia/Canberra")2025-06-25T00:00:00+10:00
* Example considering UTC as system time zone