This documentation supports releases of BMC Helix Dashboards up to December 31, 2021. To view the latest version, select the version from the Product version menu.

Supported database functions



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

  1. Frank Caruso

    How does one create custom SQL functions in Dashboards?

    Feb 01, 2023 01:02
    1. Shantanu Chatterjee

      Hi Frank,

      Thanks for writing to us. Please refer to the following documentation for more details about creating customer dashboards and queries:

      https://docs.bmc.com/docs/helixdashboards/231/creating-custom-dashboards-1161761476.html

      Thanks,
      Shantanu

      Feb 08, 2023 11:52