38 FileMaker ODBC and JDBC Guide
You must enclose date, time, and timestamp constants in braces ({}), for example, {D '2005-06-05'},
{14:35:10}, and {TS
'2005-06-05 14:35:10'}. The one exception: SQL-92 syntax requires ISO date and time
formats with no brackets:
1 DATE 'YYYY-MM-DD'
1 TIME 'HH:MM:SS'
1 TIMESTAMP 'YYYY-MM-DD HH:MM:SS'
When entering date and time values, match the format of the database file locale. For example, if the
database was created on an Italian language system, use Italian date and time formats.
Logical values that are characters must be enclosed in periods. The logical constants are .T. and 1 for True
and .F. and 0 for False. For portability, use 1 and 0.
Exponential/scientific notation
You can include exponential notation.
Example
SELECT column1, 3.4E+7 FROM table1 WHERE calc < 3.4E-6 * column2
Numeric operators
You can include the following operators in number expressions: +, -, *, /, and ^ or ** (exponentiation).
You can precede numeric expressions with a unary plus (+) or minus (-).
Character operators
You can concatenate characters.
Examples
In the following examples, last_name is 'JONES ' and first_name is 'ROBERT ':
Constant Acceptable syntax (examples)
Text 'Paris'
Number 1.05
Date DATE '2005-06-05'
{ D '2005-06-05' }
{ 06/05/2005 }
{ 06/05/05 }
Time TIME '14:35:10'
{ T '14:35:10' }
{14:35:10}
Timestamp TIMESTAMP '2005-06-05 14:35:10'
{ TS '2005-06-05 14:35:10'}
{ 06/05/2005 14:35:10 }
{2005-06-05 14:35:10}
{ 06/05/05 14:35:10 } Make sure Strict data type: 4-Digit Year Date is not selected as a
validation option in the FileMaker database file for a field using this 2-digit year syntax.
Operator Concatenation Example Result
+ Keep trailing blank characters first_name + last_name 'ROBERT JONES '
- Move trailing blank characters to the end first_name - last_name 'ROBERTJONES '
