The flat-file drivers support a number of functions that you may use in expressions. In the following tables, the functions are grouped according to the type of result they return.

Table 1. Functions that Return Character Strings
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.
Table 2. Functions that Return Numbers
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.
Table 3. Functions that Return Dates
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}.

The following examples use some of the number and date functions.

Retrieve all employees that have been with the company at least 90 days:

SELECT first_name, last_name FROM emp   

WHERE DATE() – hire_date >= 90

Retrieve all employees hired in January of this year or last year:

SELECT first_name, last_name FROM emp 
  WHERE MONTH(hire_date) = 1 
  AND (YEAR(hire_date) = YEAR(DATE())
  OR YEAR(hire_date) = YEAR(DATE()) – 1)