www.mimer.com/developer

Data Types

---

Explicit data type references are made in SQL statements for the creation of user defined domains and base tables and in the alteration of table definitions.

The permissible data types and their allowable ranges within Mimer SQL are:

Data type	Description	Range
CHARACTER(n)	Character string, fixed length n.	1 < n < 15000
CHARACTER VARYING(n) or VARCHAR(n)	Variable length character string, maximum length n.	1 < n < 15000
BINARY(n)	Fixed length binary string, maximum length n.	1 < n < 15000
BINARY VARYING(n) or VARBINARY(n)	Variable length binary string, maximum length n.	1 < n < 15000
INTEGER(p)	Integer numerical, precision p.	1 < p < 45
SMALLINT	Integer numerical, precision 5.	-32768 through 32767
INTEGER	Integer numerical, precision 10.	-2,147,483,648 through 2,147,483,647
BIGINT	Integer numerical, precision 19.	-9,223,372,036,854,775,808 through 9,223,372,036,854,775,807
DECIMAL(p,s)	Exact numerical, precision p, scale s.	1 < p < 45 0 < s < p
NUMERIC(p,s)	Exact numerical, precision p, scale s.	1 < p < 45 0 < s < p
FLOAT(p)	Approximate numerical, mantissa precision p.	1 < p < 45 Zero or absolute value 10-999 to 10+999
REAL	Approximate numerical, mantissa precision 7.	Zero or absolute value 10-38 to 10+38
FLOAT	Approximate numerical, mantissa precision 15.	Zero or absolute value 10-38 to 10+38
DOUBLE PRECISION	Approximate numerical, mantissa precision 15.	Zero or absolute value 10-38 to 10+38
DATE TIME TIMESTAMP	Composed of a number of integer fields, represents an absolute point in time, depending on sub-type.	Described below.
INTERVAL	Composed of a number of integer fields, represents a period of time, depending on the type of interval.	Described below.

In SQL, a temporal value is either a datetime (i.e. a date, a clock time, or a timestamp) or an interval (i.e. a period of time). They consist of a contiguous subset of one or more of the fields: YEAR, MONTH, DAY, HOUR, MINUTE and SECOND. Temporal values follow the usual rules of the Gregorian calendar and the 24-hour clock.

Intervals are either year-month intervals (periods of time involving years and/or months) or day-time intervals (periods of time involving days and/or hours and/or minutes and/or seconds and/or fractions of a second).

Mimer SQL supports the OVERLAPS predicate that compares either a pair of datetimes, or a datetime and an interval, to determine whether the two chronological periods overlap in time.

All numeric data and intervals may be signed. The 45 digit numeric precision also extends to arithmetic, making Mimer SQL ideally suited for applications where high numerical precision and accuracy are required.

A SEQUENCE is a construct that returns integer values regardless of concurrent access to the database system; this eliminates application contention when obtaining a unique numeric key value, a common requirement in transaction processing applications. It is also possible to retrieve the previous value returned to the application. One use for a SEQUENCE is as the default value for a column or domain.

Columns that contain an undefined value are assigned a NULL value. Depending on the context, this is represented in SQL statements either by the keyword NULL or by a host variable associated with an indicator variable.

Mimer SQL supports the CAST function, which explicitly converts between data types.

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se