42 FileMaker ODBC and JDBC Guide
SELECT SPACE(2) + Salespeople.Salesperson_ID AS Salesperson_ID FROM
Salespeople
SELECT STRVAL('60506') AS agg FROM Sales_Data WHERE Sales_Data.Invoice_ID
=
1
SELECT TIME() AS agg FROM Sales_Data WHERE Sales_Data.Invoice_ID = 1
SELECT USERNAME() AS agg FROM Sales_Data WHERE Sales_Data.Invoice_ID = 1
Functions that
return numbers Description Example
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/2004}) returns 1
DAY Returns the day part of a date
DAY({01/30/2004}) returns 30
YEAR Returns the year part of a date
YEAR({01/30/2004}) returns 2004
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/2004}) returns 7
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
X Returns the decimal equivalent of a hexadecimal
number
X'b9' returns 185
B Returns the decimal equivalent of a binary number
B'1001' returns 9
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, returns 0
NUMVAL('123') returns 123
VAL Converts a character string to a number; if the
character string is not a valid number, returns 0
VAL('123') returns 123
Functions that
return dates Description Example
DATE Returns today’s date If today is 11/21/2005, DATE() returns {2005-11-21}
DATEVAL Converts a character string to a date
DATEVAL('01/30/2006') returns {2006-01-30}
