Supported database functions

The following predefined functions can be used in the SQL query of your dashboards:



Function
Name

Description

CHARINDEX($1,$2,$3)Searches for one character expression inside a second character expression, returning the starting position of the first expression if found.
STRCONVERT($1,'CHARACTER4000')Converts an expression of type CLOB to an expression of type Varchar.
STRCONVERT($1,'CHARACTER200')Converts an expression of type nText to an expression of type Varchar.
STRCONVERT($1,'NUMERIC')Converts an expression of type String to an expression of type Number.
CurrentDate()Returns the current date and time. The date is returned in the DD/MM/YYYY format.
DATEDIFF('day', $1, $2)Returns the difference between two date values, in days.
DATEDIFF('ss', $1, $2)Returns the difference between two date values, in seconds.
DATE($1)Returns the current system date.
DATEADD($1,$2,$3)

Adds a specified number value (as a signed integer) to a specified datepart of an input date value, and then returns that modified value.

DAY($1)Returns the day of the month from a specified date.
FIRSTDAYOF('month',$1)Returns the day of the month.
FIRSTDAYOF('quarter',$1)Returns the day of the quarter.
FIRSTDAYOF('year',$1)Returns the day of the year.
HOUR($1)Returns the hour part of the specified time.
LENGTH($1)Returns the number of characters of an input string.
LISTAGG($1,$2,$3)Transforms values from a group of rows into a list of values that are delimited by a configurable separator.
LOWER($1)Returns a character expression after converting uppercase character data to lowercase.
LPAD($1,$2,$3)Left-pads a string with another string, to a specified length.
LTRIM($1)Returns a character expression after removing the leading blanks from the specified string.
MINUTE($1)Returns the minute part of the specified time.
datename('mm',$1)Returns the name of the month from a specified date.
SubStr(datename('mm',$1),0,2)Returns the name of the month in short format from a specified date. 
MONTH($1)

Returns an integer that represents the month of the specified date.

QUARTER($1)Returns the quarter of the year for a specified date.
REPLACESTR($1,$2,$3)Replaces all occurrences of a specified string value with another string value.
RPAD($1,$2,$3)Right-pads a string with another string, to a specified length.
RTRIM($1)Returns a character expression after removing the trailing blanks from the specified string.
SECOND($1)Returns the seconds part of the specified time.
SUBSTR($1,$2,$3)Returns part of a character for given length from start position.
UPPER($1)Returns a character expression after converting lowercase character data to uppercase.
datename('wd',$1)Returns the weekday name for a specified date.
WEEK($1)Returns an integer that represents the week number for a specified date.
WEEKDAY($1)Returns an integer that represents the weekday number for a specified date.
YEAR($1)Returns an integer that represents the year of the specified date.

Important

The above list is not exhaustive. You can define database functions manually and then use them in queries . For more information, see  Defining database functions for Remedy Smart Reporting in Remedy AR System Server.

Was this page helpful? Yes No Submitting... Thank you

Comments