ODBC and JDBC Guide

42 FileMaker ODBC and JDBC Guide
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
Operator precedence
As expressions become more complex, the order in which the expressions are evaluated becomes important.
This table shows the order in which the operators are evaluated. The operators in the first line are evaluated
first, and so on. Operators in the same line are evaluated left to right in the expression.
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}
Precedence Operator
1 Unary '-', Unary '+'
2 ^, **
3 *, /
4 +, -
5 =, <>, <, <=, >, >=, Like, Not Like, Is Null, Is Not Null, Between, In, Exists, Any, All