A function provides a quick way to perform an operation on data maintained by Sage HRMS. In most cases, you can use menus, text fields, lists, check boxes, and radio buttons to enter and manipulate data. But sometimes you need to build your own expression to specify the processing you want to perform. You might want to use a function as part of the value in an expression.
Example: The last name field of an employee
in the database is Nelson. You can use the UPPER() function to have the last name expressed in all capital letters (NELSON). The expression to select this name is:
UPPER(P_LNAME)="NELSON"
The function may have one or more parameters that are always placed in parentheses following the function name. Each parameter is separated by a comma. The format of a function: NAME(parameter1, parameter2, ...)
The following sections describe some of the common functions.
|
Function |
Description |
|---|---|
|
DATENAME(weekday, expD) |
Returns the name of the day of the week for a certain date. |
|
DATENAME(month, expD) |
Returns the name of the month for a certain date. |
|
DATE() |
Returns the system date of the computer. |
|
DAY() |
Returns the day of the month, expressed as a number. |
|
MONTH() |
Returns the month of the year, expressed as a number. |
|
YEAR() |
Returns the year, expressed as a number. |
|
Function |
Description |
|---|---|
|
CAST(expC AS DATE) |
Converts a date from character-type to date-type. |
|
CONVERT(VARCHAR, expD, 1) |
Converts date data to character data in the form MM/DD/YY. |
|
FORMAT(expD, 'yyyyMMdd') |
Converts date data to character data in the form YYYYMMDD. |
|
CAST(char AS NUMERIC) |
Converts character data into numeric data. |
|
Function |
Description |
|---|---|
|
CASE WHEN exp1 > exp2 THEN exp1 ELSE exp2 END |
Returns the maximum of two numbers or dates. |
|
CASE WHEN exp1 < exp2 THEN exp1 ELSE exp2 END |
Returns the minimum of two numbers or dates. |
|
Function |
Description |
|---|---|
|
FLOOR(expN) |
Truncates the decimal places of a number to its integer (a whole number). |
|
LOWER() |
Changes all the characters to lowercase. |
|
LTRIM() |
Removes the left-most spaces from the beginning of a field. |
|
REPLICATE(string exp, integer exp) |
Adds a specified number of characters to the beginning or end of a field. REPLICATE (string_expression, integer_expression) |
|
LTRIM(RTRIM(expC)) |
Removes all the trailing spaces (the blank spaces to the right of a field) from the field. |
|
UPPER() |
Changes all the characters to uppercase. |
Sage HRMS functions are unique to the Sage HRMS system. They are not accessible within the Expression Builder so you must manually enter it into your expression. See the alphabetical list of functions for the list and examples of using these functions.
|
Function |
Description |
|---|---|
|
expC IS NULL OR LTRIM(RTRIM(expC))='' |
Determines whether a field (except a date field) contains any data. |
|
IIF() |
Responds differently if different conditions are true. |
|
ISNULL() |
Determines whether a date field is empty. |
|
SUBSTRING() |
Extracts a specific portion of a field. SUBSTRING(expC, start, length) |