| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Calculates the employee's age as a numeric field with two decimal positions. | AGE(Date1,Date2) | Date1 is the date of birth; date2 is the basis for calculating the age. Neither parameter is required. | AGE({01/01/1960}, {07/01/2010}) | This would calculate the age as 50.5. |
| AGE({01/01/1960}) | This would calculate the age based on a birth date of 01/01/1960, and using the system date as the basis. | |||
| AGE() | This would calculate the age, using the date of birth in the employee master file and the system date. | |||
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Calculates the annual salary of an employee up to the next 1,000. | AN1000() | No parameters are required. | AN1000() | An employee whose annual salary was 35,225 would be calculated with this function as 36,000. This might be useful if you want a management report showing relative salary levels, and do not want precise salary amounts. |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Calculates the employee's annual salary as a whole number. | ANNPAY() | No parameters are required. | ANNPAY() | This function calculates the annual salary based on other fields in the database. |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Calculates the employee's annual salary to two decimal places | ANNUAL() | No parameters are required. | ANNUAL() | This function calculates the annual salary based on other fields in the database. |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Finds the maximum of two numbers or dates. | CASE WHEN exp1 > exp2 THEN exp1 ELSE exp2 END | exp1 and exp2 must both be numeric- or date-type. |
CASE WHEN p_annual> 150700 THEN p_annual ELSE 150700 END AS 'MAX'
|
Compares the annual salary to $150,700 and gives you whichever is greater. |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Finds the minimum of two numbers or dates. |
CASE WHEN exp1 < exp2 THEN exp1 ELSE exp2 END |
exp1 and exp2 must both be numeric-type. |
Compares the annual salary to $150,700 and gives you whichever is less. CASE WHEN p_annual< 150700 THEN p_annual ELSE 150700 END AS 'MIN' |
|
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Converts a date from character-type to date-type. |
CAST(expC AS DATE) |
Date must be character-type, in the format MM/DD/YY. | CAST(expC AS DATE) | Use when you are entering a date in an expression and want to do date arithmetic, or if you have stored dates in character-type miscellaneous fields. |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Counts the number of dependents. | COUNTDEP (Char1,Char2) |
Char1 is the benefit plan code; Char2 specifies the type of dependent to count. Neither parameter is required. | COUNTDEP() | Counts all dependents. |
| COUNTDEP("All') | Counts all dependents. | |||
|
COUNTDEP ("Insured only") |
Counts the number of insured-only dependents. | |||
| COUNTDEP("HMO 2026") | Counts all dependents with the benefit plan code "HMO 2026." | |||
| COUNTDEP ("HMO 2026,"CHILD") |
Counts the number of child dependents with the benefit plan code "HMO 2026." | |||
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Converts date-type data to character-type data in the form MM/DD/YY. |
CONVERT(VARCHAR, expD, 1) |
Date must be date-type. |
In order to change the original hire date field to character-type so that you can use it with character-type fields, use: P_LNAME + P_FNAME + CONVERT(VARCHAR, P_ORIGHIRE, 1) |
|
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Returns the system date of the computer. | DATE() | None are required. | DATE() | If today's date is July 10, 2026, this example will give you 07/10/20. |
| DATE() + 180 | If today's date is January 1, 20262, this example will give you 06/30/20. | |||
| The system date should be today's date. If DATE() does not give you today's date, use Windows help for instructions on how to reset the date. You'll use this function frequently in performing date arithmetic. | ||||
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Returns the name of the month for a certain date. |
DATENAME(month, expD) |
Date must be date-type. | DATENAME(month, expD) | If the system date is July 10,2026, this example will give you the name of the month, July. |
| DATENAME(month, P_BIRTH) AS 'Birth Month' | Gives you the birth date month of all employees. | |||
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Returns the name of the day of the week. |
DATENAME(weekday, expD) |
Date must be date-type |
DATENAME(weekday, expD) |
If the system date is July 2, 2026, this example will give you the name of the day, Wednesday. |
| DATENAME(weekday, p_orighire) AS 'Hire Day' | Gives the day of the week an employee was originally hired. | |||
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Returns the day of the week, expressed as a number. | DAY(Date) | Date must be date-type. | DAY(DATE()) | If the system date is July 10, 2026, this example will give you the exact day,10, in numeric-type. |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Calculates a dependent's age. | DEPENDAGE(Date) | Date is the basis for calculating the age. No parameter is required. | DEPENDAGE({07/01/2026}) | This would calculate the dependent's age as of July 1, 2026. |
| DEPENDAGE() | This would calculate the age, using the date of birth in the employee master file and the system date. | |||
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Returns whether or not a field contains any data. |
expC IS NULL OR LTRIM(RTRIM(expC))='' |
Data can be any type. |
expC IS NULL OR LTRIM(RTRIM(expC))='' |
This returns whether or not a field contains any data. |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Truncates a number to its integer; i.e., to the whole number. |
FLOOR(expN) |
expN is the integer to be truncated. |
FLOOR(10000.24) = 10,000 FLOOR(p_annual) Truncates a number to its integer; i.e., to the whole number. Truncates the decimal portion |
|
| Description | Parameters | Usage and Examples | |||||||
|---|---|---|---|---|---|---|---|---|---|
| Converts date-type data to character-type data in the form YYYYMMDD. | FORMAT(expD, 'yyyyMMdd') | Date must be date-type. | FORMAT(expD, 'yyyyMMdd') | If the system date is July 10, 2026, this example gives you 20260710 in character-type data. | |||||
|
When you use FORMAT(expD, 'yyyyMMdd') it sorts by the string, which puts dates into alphabetical order
|
|||||||||
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Allows the system to respond differently if different conditions are true. | IIF(Cri,Exp1,Exp2) | Cri is the criterion; if it is true, the result is the first element, Exp1. If Cri is false, the result is the second element, Exp2. The Parameters can be of any type. | The expression IIF(P_Sex = M', Mr.', Ms.') says if the gender is male, use Mr'. If not male, use Ms'. | |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Returns whether a date field is empty. | NULL(Date) | Date must be date-type. | IIF((Isnull(hbene.b_expdate) or hbene.b_expdate > Date()), " X"," ") | |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Changes all the characters to lowercase. | LOWER(Char) | Char is the field to be changed and must be character-type. | LOWER() | This is the opposite of UPPER(). See UPPER() for usage and examples. |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Removes the left-most spaces (the blank spaces in front of a character field) from the field. | LTRIM(Char) | Char must be character-type. | If you have created miscellaneous fields with leading spaces, this function will be useful in mass update to remove spaces. | |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Removes all the trailing spaces (the blank spaces to the right of a field) from the field. | LTRIM(RTRIM(expC)) | Char is the field you want to trim and must be character-type. | LTRIM(RTRIM(expC)) |
Used when you join two character fields together and you want the columns in the report to be aligned. LTRIM(RTRIM(hr.p_lname)) + ', ' + LTRIM(RTRIM(hr.p_fname) gives you the last name, followed by a comma and a space, followed by the first name, with no extra spaces. The result will be JONES, FRED. |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Returns the month of the year expressed as a number. | MONTH(Date) | Data must be date-type. | To find all employees who were originally hired in the first month, or January, use MONTH(P_ORIGHIRE) = 1. | |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Calculates the employee's salary on a monthly basis. | MONTHLY() | No parameters are required. | MONTHLY() | Since the employee's salary is not maintained as a single number, use MONTHLY() to calculate the employee's monthly salary. |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Adds a specified number of spaces to a field so that it is a predetermined length. | SPACE(Num) | Num is the specified number of spaces. | SPACE() |
It is very useful to make sure all your columns are aligned correctly. If you TRIM() the spaces from fields and then add them together to form a new item, the results might not have the same lengths. This can cause your columns not to align correctly. For example: P_LNAME + SPACE(15) Employee's last name plus 15 spaces. |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Converts numbers to characters. | STR(Num,L,D) | Num is the number that you want to convert; L is the number of characters you want the result to have, and D is the number of decimals. L and D are optional. | STR(P_SALARY,10,2) | Gives you the salary with the result being ten characters long with two decimal places (for example, 9999999.99). Even though the result looks like a number, it is now character-type data. |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Extracts a specific portion of a field. | SUBSTR(Char,N1,N2) | Char is the name of the field from which you want to extract data and must be character-type. N1 is the position of the first character that you want to extract. N2 is the number of characters to be extracted and is an optional argument. If omitted, data is extracted from N1 to the end of the field. |
You might want to run a report on all employees who live in area code 813. To select the correct employees, you have to extract the area code from the phone number. To find those employees whose area code is 813, use the expression SUBSTR(P_HPHONE,2,3) = '813'. This example assumes that the area code is enclosed with parentheses: (813). The first position is the open parenthesis and the second position is the 8. |
|
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Changes all the characters in a field to uppercase letters. | UPPER(Char) | Char is the field to be changed and must be character-type. | UPPER() |
Useful when you are searching for exact matches because it eliminates problems resulting from differences in capitalization. To find all employees with the last name Adams or ADAMS, use UPPER(P_LNAME) = "ADAMS". Without using UPPER(), you would have only found employees named ADAMS whose last names were entered in all uppercase letters. |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Changes character-type data into numeric-type data. | VAL(Char) | Char is the data to be changed and must be character-type. | VAL() | This is very useful when sorting. The computer sorts character-type numbers 1, 10, 11, 12, 2, 20, 21, etc. To sort numbers correctly, they must be numeric-type, in which case they will be sorted 1, 2, 10, 11, 12, 20, 21, etc. |
| VAL(P_MISC2) | If you have entered a number in the MISC2 field (which is a character-type field) and now want to be able to add up all the numbers for a range of employees, this example will convert the data-type to numeric-type so that the system can perform addition. | |||
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Returns the year expressed as a number. | YEAR(Date) | Date is the date, and must be date-type. | YEAR(DATE()) |
If the system date is July 10, 2026, this example will give you 2026. To select employees whose date of original hire is 2026, use YEAR(P_ORIGHIRE) = 2026. |
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Calculates the employee's age as a whole
number. (The AGE() function calculates the age including two decimal places.) Use YEARSOLD() when you need to compute an employee's age for benefits calculations. |
YEARSOLD (Date1,Date2) |
Date1 is the date of birth; Date2 is the basis for calculating the age. Neither parameter is required. | YEARSOLD ({01/01/1967}, {07/01/2014}) |
This would calculate the age as 47. |
| YEARSOLD ({01/01/1970}) |
This would calculate the age based on a birth date of 01/01/1970, using the system date as the basis. | |||
| YEARSOLD() | This would calculate the age, using the date of birth in the employee personnel file and the system date. | |||