All date fields contain the time in addition to the date. If you use dates in calculations you should remove the time part of the date using the TtoD() function.
For example:
INT((Date() - TtoD(hrpersnl.p_birth))/365)
TtoD(hrpersnl.p_lasthire) + 90
You may also want to remove the time from the date when displaying or printing dates.
List of Functions
AGE()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| This function 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. | |||
AN1000()
| 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. |
ANNPAY()
| 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. |
ANNUAL()
| 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. |
CDOW()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Gives you the name of the day of the week. | CDOW(Date) | Date must be date-type | CDOW(DATE()) | If the system date is July 2, 2016, this example will give you the name of the day, Wednesday. |
| CDOW(DATE()-10) | Gives you the current date and subtracts ten (10) days, giving you the name of the day. | |||
| CDOW(P_ORIGHIRE) | Gives the day of the week an employee was originally hired. | |||
CMONTH()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Gives you the name of the month for a certain date. | CMONTH(Date) | Date must be date-type. | CMONTH(DATE()) | If the system date is July 10,2016, this example will give you the name of the month, July. |
| CMONTH(P_ORIGHIRE) = 'July' | Gives you all employees whose original hire date is in July. | |||
COUNTDEP()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| You can use COUNTDEP() to count 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 2016") | Counts all dependents with the benefit plan code "HMO 2016." | |||
| COUNTDEP ("HMO 2016,"CHILD") |
Counts the number of child dependents with the benefit plan code "HMO 2016." | |||
CTOD()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Converts a date from character-type to date-type. | CTOD(Date) | Date must be character-type, in the format MM/DD/YY. | CTOD() | 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. |
| CTOD('01/01/17') | Takes the character-type date and converts it to 01/01/17 in date-type. | |||
| CTOD('01/01/17')+180 | Takes the character-type date, converts it to 01/01/17 in date-type, adds 180 days, and gives you 06/30/17 in date-type. | |||
DATE()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Gives you the system date of the computer. | DATE() | None are required. | DATE() | If today's date is July 10, 2016, this example will give you 07/10/16. |
| DATE() + 180 | If today's date is January 1, 20162, this example will give you 06/30/16. | |||
| 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. | ||||
DAY()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Gives you 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, 2016, this example will give you the exact day,10, in numeric-type. |
DEPENDAGE()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| This function calculates a dependent's age. | DEPENDAGE(Date) | Date is the basis for calculating the age. No parameter is required. | DEPENDAGE({07/01/2016}) | This would calculate the dependent's age as of July 1, 2016. |
| DEPENDAGE() | This would calculate the age, using the date of birth in the employee master file and the system date. | |||
DEPENDSMOKER()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Returns whether or not a dependent is a smoker. | DEPENDSMOKER() | No parameters are required. | DEPENDSMOKER() | This returns Yes if the dependent is a smoker; No if the dependent is not. If you use this function in an expression in a benefit plan rate table, set the expression as: iif(dependsmoker()'Y','N') and set the columns as Y and N. |
DESC()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| This function accesses code table descriptions. | DESC(Char,Num) | Char is the name of the data item for which you want a description. Num is the desired length of the description. Only the first parameter is required. Without Num, a length of 25 is assumed. If Num is zero, the function will remove any trailing spaces. | DESC(P_LEVEL1) | This would return the description corresponding to organization level 1. A length of 25 is assumed. |
| DESC(P_LEVEL1,30) | This would return the description corresponding to organization level 1. The description length would be 30. | |||
DTOC()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Converts date-type data to character-type data in the form MM/DD/YY. | DTOC(Date) | 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 + DTOC(P_ORIGHIRE) Do not use DTOC() to sort by date because it sorts by month, day and year, which is not the logical chronological order: 03/01/61 Instead, use DTOS() as described below. |
|
DTOS()
| Description | Parameters | Usage and Examples | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Converts date-type data to character-type data in the form YYYYMMDD. | DTOS(Date) | Date must be date-type. | DTOS(DATE()) | If the system date is July 10, 2016, this example gives you 20160710 in character-type data. | |||||||||
|
When you use DTOS() it sorts by year, month and day, which puts dates into logical chronological order (compare to DTOC() above):
|
|||||||||||||
EMPTY()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Determines whether or not a field contains any data. | EMPTY(Data) | Data can be any type. | EMPTY() | This returns whether or not a field contains any data. |
IIF()
| 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'. | |
INT()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Truncates a number to its integer; i.e., to the whole number. | INT(Num) | Num is the integer to be truncated. | INT(10000.24) = 10,000 | |
ISNULL()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Use to evaluate whether a date field is empty. | NULL(Date) | Date must be date-type. | IIF((Isnull(hbene.b_expdate) or hbene.b_expdate > Date()), " X"," ") | |
LOWER()
| 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. |
LTRIM()
| 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. | |
MAX()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Finds the maximum of two numbers or dates. | MAX(Num1,Num2) | Num1 and Num2 must both be numeric- or date-type. | MAX(P_SALARY, 50000) | Compares the salary to $50,000 and gives you whichever is greater. |
MIN()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Finds the minimum of two numbers or dates. | MIN(Num1,Num2) | Num1 and Num2 must both be numeric-type. | The expression MIN(P_SALARY,50000) compares the salary to $50,000 and gives you whichever is less. | |
MONTH()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Gives you 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. | |
MONTHLY()
| 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. |
SPACE()
| 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. |
STR()
| 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. |
SUBSTR()
| 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. |
|
TRIM()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Removes all the trailing spaces (the blank spaces to the right of a field) from the field. | TRIM(Char) | Char is the field you want to trim and must be character-type. | TRIM() |
Used when you join two character fields together and you want the columns in the report to be aligned. TRIM(P_LNAME + ', ' + TRIM(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. |
UPPER()
| 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. |
VAL()
| 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. | |||
YEAR()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| Gives you 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, 2016, this example will give you 2016. To select employees whose date of original hire is 2016, use YEAR(P_ORIGHIRE) = 2016. |
YEARSOLD()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| This function calculates the employee's age as a whole number. The AGE() function calculates the age including two decimal places. You might want to 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/2013}) |
This would calculate the age as 46. |
| 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. | |||
YEARSSENIORITY()
| Description | Parameters | Usage and Examples | ||
|---|---|---|---|---|
| This function calculates active and LOA employees number of years with the company. You might want to use this function in a rate table for a savings plan whose employer match is based on an employee's number of years with the company. | YEARSSENIORITY() | No parameters are required. | YEARSSENIORITY() | This calculates the years seniority, using the seniority date in the employee personnel file and the system date. If the employee is terminated, the function uses the termination date instead of the system date. |