Function | Description |
CHR | Converts an ASCII code into a one-character string. CHR(67) returns C. |
RTRIM | Removes trailing blanks from a string. RTRIM('ABC ') returns ABC. |
TRIM | Removes trailing blanks from a string. TRIM('ABC ') returns ABC. |
LTRIM | Removes leading blanks from a string. LTRIM(' ABC') returns ABC. |
UPPER | Changes each letter of a string to uppercase. UPPER('Allen') returns ALLEN. |
LOWER | Changes each letter of a string to lowercase. LOWER('Allen') returns allen. |
LEFT | Returns leftmost characters of a string. LEFT('Mattson',3) returns Mat. |
RIGHT | Returns rightmost characters of a string. RIGHT('Mattson',4) returns tson. |
SUBSTR | Returns a substring of a string. Parameters are the string, the first character to extract, and the number of characters to extract (optional). SUBSTR('Conrad',2,3) returns onr. SUBSTR('Conrad',2) returns onrad. |
SPACE | Generates a string of blanks. SPACE(5) returns ' '. |
DTOC | Converts a date to a character string. An optional second parameter determines the format of the result: 0 (the default) returns MM/DD/YY. 1 returns DD/MM/YY. 2 returns YY/MM/DD. 10 returns MM/DD/YYYY. 11 returns DD/MM/YYYY. 12 returns YYYY/MM/DD. An optional third parameter specifies the date separator character. If not specified, a slash (/) is used. DTOC({01/30/1997}) returns 01/30/97. DTOC({01/30/1997}, 0) returns 01/30/97. DTOC({01/30/1997}, 1) returns 30/01/97. DTOC({01/30/1997}, 2,'-') returns 97-01-30. |
DTOS | Converts a date to a character string using the format YYYYMMDD. DTOS({01/23/1990}) returns 19900123. |
IIF | Returns one of two values, true or false. Parameters are a logical expression, the true value, and the false value. If the logical expression evaluates to true, the function returns the true value. Otherwise, it returns the false value. IIF(salary>20000,'BIG','SMALL') returns BIG if salary is greater than 20000. If not, it returns SMALL. |
STR | Converts a number to a character string. Parameters are the number, the total number of output characters (including the decimal point), and optionally the number of digits to the right of the decimal point. STR(12.34567,4) returns 12. STR(12.34567,4,1) returns 12.3. STR(12.34567,6,3) returns 12.346. |
STRVAL | Converts a value of any type to a character string. STRVAL('Woltman') returns Woltman. STRVAL({12/25/1953}) returns 12/25/1953. STRVAL (5 * 3) returns 15. STRVAL (4 = 5) returns 'False'. |
TIME | Returns the time of day as a character string.At 9:49 PM, TIME() returns 21:49:00. |
TTOC | Note: This function applies only to flat-file drivers that support SQL_TIMESTAMP: the Btrieve driver and the dBASE (access to FoxPro 3.0) driver. Converts a timestamp to a character string. An optional second parameter determines the format of the result: When set to 0 or none (the default), MM/DD/YY HH:MM:SS AM is returned. When set to 1, YYYYMMDDHHMMSS is returned, which is a suitable format for indexing. TTOC({1992-04-02 03:27:41}) returns 04/02/92 03:27:41 AM. TTOC({1992-04-02 03:27:41, 1}) returns 19920402032741 |
USERNAME | For Btrieve, the logon ID specified at connect time is returned. For all other flat-file drivers, an empty string is returned. |
Function | Description |
MOD | Divides two numbers and returns the remainder of the division. MOD(10,3) returns 1. |
LEN | Returns the length of a string. LEN('ABC') returns 3. |
MONTH | Returns the month part of a date. MONTH({01/30/1989}) returns 1. |
DAY | Returns the day part of a date. DAY({01/30/1989}) returns 30. |
YEAR | Returns the year part of a date. YEAR({01/30/1989}) returns 1989. |
MAX | Returns the larger of two numbers. MAX(66,89) returns 89. |
DAYOFWEEK | Returns the day of week (1-7) of a date expression. DAYOFWEEK({05/01/1995}) returns 5. |
MIN | Returns the smaller of two numbers. MIN(66,89) returns 66. |
POW | Raises a number to a power. POW(7,2) returns 49. |
INT | Returns the integer part of a number .INT(6.4321) returns 6. |
ROUND | Rounds a number. ROUND(123.456, 0) returns 123. ROUND(123.456, 2) returns 123.46. ROUND(123.456, –2) returns 100. |
NUMVAL | Converts a character string to a number. If the character string is not a valid number, a zero (0) is returned. NUMVAL('123') returns the number 123. |
VAL | Converts a character string to a number. If the character string is not a valid number, a zero (0) is returned. VAL('123') returns the number 123. |
Function | Description |
DATE | Returns today’s date. If today is 12/25/1999, DATE() returns {12/25/1999}. |
TODAY | Returns today's date. If today is 12/25/1999, TODAY() returns {12/25/1999}. |
DATEVAL | Converts a character string to a date. DATEVAL('01/30/1989') returns {01/30/1989}. |
CTOD | Converts a character string to a date. An optional second parameter specifies the format of the character string: 0 (the default) returns MM/DD/YY, 1 returns DD/MM/YY, and 2 returns YY/MM/DD. CTOD('01/30/1989') returns {01/30/1989}. CTOD('01/30/1989',1) returns {30/01/1989}. |