Function Reference Guide with Examples
This document explains common system functions (such as mathematical, string, and date
functions) used in HR or analytics systems, along with sample use cases.
1. Mathematical Functions
ABS(): Returns the absolute (positive) value of a number.
Example: ABS(-20) → 20
ROUND(): Rounds a number to the nearest integer or decimal point.
Example: ROUND(12.56, 1) → 12.6
POWER(): Raises a number to a power.
Example: POWER(2, 3) → 8
LOG(): Returns the natural logarithm (base e).
Example: LOG(10) → 2.3026
LOG10(): Returns the base-10 logarithm.
Example: LOG10(1000) → 3
DOUBLE(): Converts a value to double-precision format.
Example: DOUBLE('15.5') → 15.5
FLOAT(): Converts a value to a floating-point number.
Example: FLOAT('10') → 10.0
TOINTEGER(): Converts text or number to an integer.
Example: TOINTEGER('123.9') → 123
TONUMBER(): Converts text to number type.
Example: TONUMBER('45') → 45
2. String Functions
CONCAT(): Joins two or more strings.
Example: CONCAT('John', ' ', 'Doe') → John Doe
LENGTH(): Returns number of characters in a string.
Example: LENGTH('Hello') → 5
LEFT(): Extracts characters from the left.
Example: LEFT('Employee', 3) → Emp
RIGHT(): Extracts characters from the right.
Example: RIGHT('Employee', 3) → yee
SUBSTRING(): Extracts substring from a string.
Example: SUBSTRING('Employee', 2, 3) → 'mpl'
UPPERCASE(): Converts text to upper case.
Example: UPPERCASE('john') → JOHN
LOWERCASE(): Converts text to lower case.
Example: LOWERCASE('JOHN') → john
REPLACE(): Replaces part of text with another.
Example: REPLACE('2023-01-01','-','/') → 2023/01/01
LIKE(): Checks if text matches a pattern.
Example: LIKE('HR_Dept','HR%') → TRUE
LOCATE(): Finds the position of a substring.
Example: LOCATE('l','Hello') → 3
CONTAIN(): Checks if a string contains another.
Example: CONTAIN('Employee','Emp') → TRUE
NOT(): Negates a boolean condition.
Example: NOT(TRUE) → FALSE
3. Logical and Conditional Functions
IF(): Performs a conditional test and returns results accordingly.
Example: IF(Salary>50000,'High','Low')
ISNULL(): Checks if a value is null.
Example: ISNULL([Employee#Department]) → TRUE/FALSE
4. Date and Time Functions
DAY(): Returns day of the month.
Example: DAY('2025-10-04') → 4
MONTH(): Returns month number.
Example: MONTH('2025-10-04') → 10
YEAR(): Returns year.
Example: YEAR('2025-10-04') → 2025
WEEK(): Returns week number of year.
Example: WEEK('2025-10-04') → 40
QUARTER(): Returns quarter of year.
Example: QUARTER('2025-10-04') → 4
TODATE(): Converts text to a date type.
Example: TODATE('2025-10-04','YYYY-MM-DD') → 2025-10-04
DAYS_BETWEEN(): Calculates difference between two dates in days.
Example: DAYS_BETWEEN('2025-10-01','2025-10-04') → 3
ADDDAYTODATE(): Adds days to a date.
Example: ADDDAYTODATE('2025-10-01',3) → 2025-10-04
ADDMONTHTODATE(): Adds months to a date.
Example: ADDMONTHTODATE('2025-01-01',6) → 2025-07-01
UTCTOLOCAL(): Converts UTC time to specified time zone.
Example: UTCTOLOCAL('2025-10-04T10:00Z','IST') → 2025-10-04 15:30
5. Conversion and Utility Functions
TOTEXT(): Converts a number or date to text.
Example: TOTEXT(123) → '123'