Data Types

Manifold SQL data types consist of several primary data types and several valid synonyms recognized for these data types.

The following table lists the primary data types. The synonyms are identified in the SQL Reserved Words / Index topic.

Data type	Storage size	Description
BINARY	1 byte per character	Any type of data may be stored in a column of this type. No translation of the data (for example, to text) is made. How the data is input in a binary column dictates how it will appear as output.
BIT	1 byte	Yes and No values and columns that contain only one of two values.
BYTE	1 byte	An integer value between 0 and 255.
COORDSYS	variable	A coordinate system token.
CURRENCY	8 bytes	A scaled integer between -9.2E14 and 9.2E14.
DATETIME	16 bytes	A date or time value between the years 100 and 9999.
DOUBLE	8 bytes	A double-precision floating-point value with a range of -1.8E308 to 1.8E308
GEOM	variable	A geometric object, includes coordinate system data.
GEOMSDE	variable	A geometric object in ESRI ArcSDE format.
GEOMSHP	variable	A geometric object in ESRI SHP format (geodatabase).
GEOMWKB	variable	A geometric object in OGC WKB format.
LATITUDE	8 bytes	A double-precision floating-point latitude value.
LONG	4 bytes	A long integer between - 2,147,483,648 and 2,147,483,647.
LONGITUDE	8 bytes	A double-precision floating-point longitude value.
SHORT	2 bytes	A short integer between - 32,768 and 32,767.
SINGLE	4 bytes	A single-precision floating-point value with a range of -3.4E38 to 3.4E38.
TEXT	2 bytes per character (Unicode)	Zero to a maximum of 2 gigabytes.

Synonyms

The following table lists synonym names for the primary data types used in Manifold SQL:

Synonym	Data Type
CHAR	TEXT
CHARACTER	TEXT
DATE	DATETIME
INT	LONG
INTEGER	LONG
LONGBINARY	BINARY
LONGTEXT	TEXT
MONEY	CURRENCY
REAL	SINGLE
SMALLINT	SHORT
TIME	DATETIME
TIMESTAMP	DATETIME
TINYINT	BYTE
VARCHAR	TEXT

String Literals

SQL is case insensitive for SQL itself but is case sensitive when evaluating string values. Suppose, for example, we have a query such as:

SELECT [F] FROM [Table] WHERE [F] = "13AF";

This query will return no results if the value in "F" is "13aF", because that is not the same as "13AF".

To normalize case, use the Transform toolbar Make Lower Case or Make Upper Case operators to force all values in the column to upper case or lower case. Alternatively, use a modified SELECT statement:

SELECT [F] FROM [Table] WHERE UCase([F]) = "13AF";

When comparing strings, SQL is sensitive to leading or trailing whitespace. "13AF " does not equal "13AF". To remove leading / trailing whitespace use the Transform toolbar Trim Left, Trim Right or Trim operators to eliminate leading, trailing or both leading and trailing whitespace. Alternatively, use a modified SELECT statement:

SELECT [F] FROM [Table] WHERE Trim([F]) = "13AF";

The strings are enclosed either in apostrophes (') or in quotes (") depending on whether the query is ANSI-compatible or not.

Date Literals

The date literals used in SQL must use either ANSI format (year-month-day hour:minute:second) or one of the formats mentioned in the Regional Options applet in the Windows Control Panel.

To find records dated July 10, 1992 on a machine with US regional options, use either of the following statements:

SELECT * FROM [Orders] WHERE [Shipped Date] = #92-7-10#;

SELECT * FROM [Orders] WHERE [Shipped Date] = #7/10/92#;

The dates are enclosed in hash signs (#). They can also be enclosed in apostrophes (') or in quotes (") depending on whether the query is ANSI-compatible or not.