You are viewing content for 5.1 | 5.0 | 4.9 | 4.8 | 4.7 | 4.6 | 4.5 | 4.4 | 4.3 | Previous Releases


Formula Functions

Name Description
abs() Returns the absolute value of a double expression
addDays() Returns a new date after adding an integer number of days to a given date
addHours() Returns a new date after adding an integer number of hours to a given date
addMilliseconds() Returns a modified date after the specified milliseconds have been added
addMinutes() Returns a modified date after the specified minutes have been added
addMonths() Returns a modified date after the specified minutes have been added
addSeconds() Returns a modified date after the specified number of seconds have been added
addYears() Returns the result of adding years to a date
and() Returns true if a series of boolean expressions all evaluate to true, returning false otherwise
average() Returns the average of the values in a column
between() Returns true if a given value lies between a given range, returning false otherwise
bin() Splits data values based on specified criteria
case() Returns one a a series of expressions depending on whether a series of tests return true or false
ceil() Returns the smallest integer greater than or equal to its argument
concat() Returns the concatenation of an arbitrary number of string expressions
contains() Returns whether a string contains a given substring
count() Returns the number of elements (rows) in a column
date() Returns a date value converted from a string
dateTrunc() Returns a timestamp value truncated to a specified unit of time (such as days or minutes)
day() Returns the day of the month
daysBetween() Returns the number of days between two dates
decode() UNK
descendantOf() Returns true for each record (in the “FIRST_NAME” column) that is descendant of “John”, i.e. whose manager is “John”
distinct() Returns the number of different groups of elements in a column
double() Returns a double given a string
endsWith() Returns whether a string ends with a given substring
epoch() Returns the number of seconds since the epoch given a timestamp
exp() Raises e to a power
find() Returns the index of the first occurence of a string within another string
findLast() Returns the index of the last substring match
firstVersion() Returns the first occurence of a value with respect to a specified order
floor() Returns the largest integer less than or equal to its argument
formatDate() Returns a date from a string given a format
formatDuration()
hour() Returns the hour field (hh) of a date or timestamp value
if() Returns one of two expressions depending on whether a thrid expression returns true or false
ifNull() Returns one of two expressions depending on the first argument
ifNull()
in() Returns true if any of a list of items match the given expression and false otherwise
inList() Return true if an expression evaluates to one or more constants
int() Returns an int given a double
isNan() Returns true if a given value is NaN and false otherwise
isNull() Returns true of a value is NULL and false otherwise
lTrim() Returns its string argument without any leading whitespace
lastVersion() Returns the last occurrence of a value with respect to a specified order
length() Returns the length (number of characters) of a string
like() Returns true if a substring can be found in a string
long() Returns long given a double
lookup() Returns a value from a table using primary key values
lower() Returns a lower case string given a mixed case string
max() Returns the maximum of the values in a column
max2() Returns the largest of two values passed as arguments
median() Returns the median of the values in a column
min() Returns the minimum of the values in a column
min2() Returns the smaller of two values passed as arguments
minute() Returns the minute (mm) field of a date or timestamp value
mod() Returns the modulus (remainder) resulting from the division of one double by another
month() Returns the month (MM) field of a date or timestamp value
not() Returns false if its argument is true and true if its argument is false
or() Returns true if any of a series of boolean expressions evaluate to true, returning false otherwise
parseDate() Returns a date value from a date formatted string
parseDouble() Returns double from a string
parseTimestamp() Returns a timestamp from a formatted string
quarter() Returns the quarter corresponding to a date or timestamp expression
rTrim() Returns its string argument without any trailing whitespace
removeTime() Returns a timestamp value with the time fields removed
repeat() Returns a given string repeated a specified number of times
replace() Returns a string with substrings replaced by new substrings
reverse() Returns the reverse of its string argument
rnd() Returns a random number with value between 0 and 1
round() Returns the closest integer to its floating point argument
rowNumber() Returns a sequential number of a row within an Insight result set
schemaRefreshTime() Returns the last refresh start time if the system is running
second() Returns the seconds (ss) component of a timestamp or date expression
splitPart() Returns an element of a delimited string
sqrt() Returns the square root of a number
startsWith() Returns whether a string starts with a given substring
string() Returns a string value from an int
subString() Returns a substring from a string expression
sum() Find the sum of the values in a column
timeBetween() Returns the interval between two given times
timestamp() Returns a timestamp value from a string
toChar() Returns a string type given a date type
trim() Returns its string argument without leading or trailing whitespace
trunc() Returns the whole number portion of a double
upper() Returns an upper case string given a string of mixed case
weekday() Returns the day number of the week from a day/time string
weeknum() Returns the week number from the year in the argument
year() Returns the year field of a date or timestamp value

© Incorta, Inc. All Rights Reserved.