SQL Statements for Flat-File Drivers : Select Statement : SQL Expressions : Functions

Functions
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.
CHR(67) returns C.
RTRIM('ABC  ') returns ABC.
TRIM('ABC  ') returns ABC.
LTRIM('  ABC') returns ABC.
UPPER('Allen') returns ALLEN.
LOWER('Allen') returns allen.
LEFT('Mattson',3) returns Mat.
RIGHT('Mattson',4) returns tson.
SUBSTR('Conrad',2) returns onrad.
SPACE(5) returns '     '.
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({01/23/1990}) returns 19900123.
IIF(salary>20000,'BIG','SMALL') returns BIG if salary is greater than 20000. If not, it returns SMALL.
STR(12.34567,4) returns 12.
STR(12.34567,4,1) returns 12.3.
STR(12.34567,6,3) returns 12.346.
STRVAL('Woltman') returns Woltman.
STRVAL({12/25/1953}) returns 12/25/1953.
STRVAL (5 * 3) returns 15.
STRVAL (4 = 5) returns 'False'.
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
MOD(10,3) returns 1.
LEN('ABC') returns 3.
DAY({01/30/1989}) returns 30.
YEAR({01/30/1989}) returns 1989.
MAX(66,89) returns 89.
MIN(66,89) returns 66.
POW(7,2) returns 49.
.INT(6.4321) returns 6.
ROUND(123.456, 0) returns 123.
ROUND(123.456, 2) returns 123.46.
NUMVAL('123') returns the number 123.
VAL('123') returns the number 123.
If today is 12/25/1999, DATE() returns {12/25/1999}.
If today is 12/25/1999, TODAY() returns {12/25/1999}.
DATEVAL('01/30/1989') returns {01/30/1989}.
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)

© 2013 Progress Software Corporation and/or its subsidiaries or affiliates.