www.mimer.com/developer

Data Types in SQL Statements

---

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

The permissible data types and their ranges are:

Data type	Description	Range
CHARACTER(n)	Character string, fixed length n. See	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. (Same as DECIMAL - see below).	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 16.	Zero or absolute value 10-308 to 10+308
DOUBLE PRECISION	Approximate numerical mantissa precision 16.	Zero or absolute value 10-308 to 10+308
DATE TIME TIMESTAMP	Composed of a number of integer fields, represents an absolute point in time, depending on sub-type.	* Refer to DATE, TIME and TIMESTAMP for a complete explanation of this data type.
INTERVAL	Composed of a number of integer fields, represents a period of time, depending on the type of interval.	* Refer to Interval for a complete explanation of this data type.

Pseudo Data Type

There is an additional pseudo data type supported by Mimer SQL, called the ROW data type.

A ROW data type definition can be specified where one of the above data types would normally be used in a variable declaration in a compound statement, see the Mimer SQL Programmer's Manual for details.

Binary Data

Binary data may only be stored in the database and retrieved again, it cannot be used in arithmetical operations.

If binary data is retrieved into a character data type, the length of the character data type must be twice that of the binary data type to accommodate the resulting hexadecimal character string.

In interactive SQL, the way binary data is displayed depends on how the interactive tool is configured (in BSQL binary data is displayed as its hexadecimal value).

Numerical Data

All numerical data may be signed.

For all numerical data, the precision "p" indicates the maximum number of decimal digits the number may contain, excluding any sign or decimal point.

For decimal data, the scale "s" indicates the fixed number of digits following the decimal point. Note that decimal data with scale zero (DECIMAL(p,0)) is not the same as integer (INTEGER(p)).

Floating point (approximate numerical) data is stored in exponential form. The precision is specified for the mantissa only. The permissible range of the exponent is -999 to +999.

Note: In Mimer SQL the NUMERIC data type is exactly equivalent to DECIMAL.

In the following cases, the omission of scale, or the omission of both precision and scale, is allowed (scale may not be specified without precision):

CHARACTER	is equivalent to CHARACTER(1)
DECIMAL	is equivalent to DECIMAL(15,0)
DECIMAL(5)	is equivalent to DECIMAL(5,0)
NUMERIC	is equivalent to NUMERIC(15,0)
NUMERIC(5)	is equivalent to NUMERIC(5,0)

Note: The data type INTEGER is distinct from INTEGER(10).

Data Type Abbreviations

The following abbreviations are accepted for data type definitions:

Abbreviation	Character
CHAR(n)	CHARACTER(n)
CHAR	CHARACTER
CHAR VARYING(n)	CHARACTER VARYING(n)
VARCHAR(n)	CHARACTER VARYING(n)
VARBINARY(n)	BINARY VARYING(n)
INT(p)	INTEGER(p)
INT	INTEGER
DEC(p, s)	DECIMAL(p, s)
DEC	DECIMAL
NUM(p, s)	NUMERIC(p, s)
NUM	NUMERIC

DATE, TIME and TIMESTAMP

The DATE or TIMESTAMP data type represents an absolute position on the timeline and the TIME data type represents an absolute time of day.

"DATETIME" is a term used to collectively refer to the data types DATE, TIME and TIMESTAMP.

A DATETIME contains some or all of the fields YEAR, MONTH, DAY, HOUR, MINUTE and SECOND. These fields always occur in the order listed, which is from the most significant to least significant. Each of the fields is an integer value, except that the SECOND field may have an additional integer component to represent the fractional seconds.

For a DATETIME value with a SECOND component, it is possible to specify an optional seconds precision which is the number of significant digits in the fractional part of the SECOND value. This must be a value between 0 and 9. If a seconds precision is not specified, the default is 0 for TIME and 6 for TIMESTAMP.

DATE values are represented according to the Gregorian calendar and TIME values are represented according to the 24 hour clock.

The inclusive value limits for the DATETIME fields are as follows:

Field	Inclusive value limit
YEAR	0001 to 9999
MONTH	01 to 12
DAY	01 to 31 (upper limit further constrained by MONTH and YEAR)
HOUR	00 to 23
MINUTE	00 to 59
SECOND	00 to 59.999999999

The three DATETIME data types are: DATE, TIME(s) and TIMESTAMP(s).

DATE

This describes a date using the fields YEAR, MONTH and DAY in the format YYYY-MM-DD. The length is 10.

TIME(s)

This describes a time in an unspecified day, with seconds precision s, using the fields HOUR, MINUTE and SECOND in the format HH:MM:SS[.sF] where F is the fractional part of the SECOND value.

If a seconds precision is not specified, s defaults to 0. The length is 8 (or 9+s, if s > 0).

TIMESTAMP(s)

This describes both a date and time, with seconds precision s, using the fields YEAR, MONTH, DAY, HOUR, MINUTE and SECOND in the format YYYY-MM-DD HH:MM:SS[.sF] where F is the fractional part of the SECOND value.

If a seconds precision is not specified, s defaults to 6. The length is 26 (or 19, if s = 0 or 20+s, if s > 0).

Interval

An INTERVAL is a period of time, such as "3 years" or "90 days" or "5 minutes and 45 seconds".

There are effectively two kinds of INTERVAL:

"YEAR-MONTH" containing one or both of the fields YEAR and MONTH.

"DAY-TIME" containing one or more consecutive fields from the set DAY, HOUR, MINUTE and SECOND.

The distinction is made between the two interval types in order to avoid the ambiguity that would arise if a MONTH value was combined with a field of lower significance, e.g. DAY, given that different months contain differing numbers of days.

For example, the hypothetical interval "2 months and 10 days" could vary between 69 and 72 days in length, depending on the months involved. Therefore, to avoid unwanted variations in the downstream arithmetic etc., the variable length MONTH component may only exist at the lowest significance level in an INTERVAL.

The SECOND field may also only exist at the lowest significance level in an INTERVAL, simply because it is the least significant of all the fields.

An INTERVAL data type is a signed numeric quantity (i.e. negative INTERVALs are allowed) comprising a specific set of fields. The list of fields in an INTERVAL is called the interval precision.

The fields in an INTERVAL are exactly the same as those previously described for DATETIME except that the value constraints imposed on the most significant field are determined by the leading precision (p in Named Interval Data Types) for the INTERVAL type and not by the Gregorian calendar and 24 hour clock.

A leading precision value between 1 and the maximum allowed for the field type may be specified for an INTERVAL. If none is specified, the default is 2.

Value Constraints for Fields in an Interval

The table below shows the maximum permitted leading precision values for each field type in an INTERVAL:

Field	Maximum leading precision
YEAR	7
MONTH	7
DAY	7
HOUR	8
MINUTE	10
SECOND	12

The value of a MONTH field, which is not in the leading field position, is constrained between 0 and 11, inclusive, in an INTERVAL (and not between 1 and 12 as in a DATETIME).

Where the SECOND field is involved, seconds precision (s in Named Interval Data Types) can be specified for it in the same way as for DATETIME.

Note that in the INTERVAL consisting only of a SECOND field ("INTERVAL SECOND"), the SECOND field will have both a leading precision and a seconds precision, specified together.

A seconds precision value between 0 and 9 may be specified for an INTERVAL. If the seconds precision is not specified, a default value of 6 is implied.

Named Interval Data Types

The syntactic element that is used to specify the interval precision, leading precision and (where appropriate) the seconds precision is the interval qualifier. This follows the keyword INTERVAL when specifying an INTERVAL data type.

The following table lists the valid interval qualifiers for YEAR-MONTH intervals:

Interval Qualifier	Description
YEAR(p)	An interval class describing a number of years, with a leading precision p. It contains a YEAR field in the format: pY.
MONTH(p)	An interval class describing a number of months, with leading precision p. It contains a MONTH field in the format: pM.
YEAR(p) TO MONTH	An interval class describing a number of years and months, with leading precision p. The format is: pY-MM.

The following table lists the valid interval qualifiers for DAY-TIME intervals:

Interval Qualifier	Description
DAY(p)	An interval class describing a number of days, with a leading precision p. It contains a DAY field in the format: pD.
HOUR(p)	An interval class describing a number of hours, with leading precision p. It contains an HOUR field in the format: pH.
MINUTE(p)	An interval class describing a number of minutes, with leading precision p. It contains a MINUTE field in the format: pM.
SECOND(p, s)	An interval class describing a number of seconds, with leading precision p and seconds precision s. It contains a SECOND field in the format: pS[.sF]. (F is the fractional part of the seconds value.)
DAY(p) TO HOUR	An interval class describing a number of days and hours, with leading precision p. The format is: pD HH.
DAY(p) TO MINUTE	An interval class describing a number of days, hours and minutes, with leading precision p. The format is: pD HH:MM.
DAY(p) TO SECOND(s)	An interval class describing a number of days, hours, minutes and seconds, with leading precision p. The format is: pD HH:MM:SS[.sF].
HOUR(p) TO MINUTE	An interval class describing a number of hours and minutes, with leading precision p. The format is: pH:MM.
HOUR(p) TO SECOND(s)	An interval class describing a number of hours, minutes and seconds, with leading precision p and seconds precision s. The format is: pH:MM:SS[.sF].
MINUTE(p) TO SECOND(s)	An interval class describing a number of minutes and seconds, with leading precision p and seconds precision s. The format is: pM:SS[.sF].

Length of an Interval

The length of an INTERVAL is the same as the number of characters required to represent it as a string and is determined by the interval precision, leading precision and the seconds precision (where it applies).

The maximum length of an INTERVAL can be computed according to the following rules:

• The length of the most significant field is the leading precision value (p).
• Allow a length of 2 for each field following the most significant field.
• Allow a length of 1 for each separator between fields. Separators occur between YEAR and MONTH, DAY and HOUR, HOUR and MINUTE, and MINUTE and SECOND.
• If seconds precision applies, and is non-zero, allow a length equal to the seconds precision value, plus 1 for the decimal point preceding the fractional part of the seconds value.

The NULL Value

Columns which 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 whose value is minus one, see the Mimer SQL Programmer's Manual.

The NULL value is generally never equal to any value, not even to itself. All comparisons involving NULL evaluate to "unknown", see Comparisons.

Note: NULL values are treated as equal to each other for the purposes of DISTINCT, GROUP BY, and ORDER BY.

NULL is sorted at the end of ascending sequences and at the beginning of descending sequences.

Data Type Compatibility

Assignment and comparison operations generally require that the data types of the items involved (literals, variables or column values) are compatible but not necessarily exactly equivalent.

Any exceptions to this rule are specified in the detailed syntax descriptions in SQL Language Elements and SQL Statement Descriptions.

All character data is compatible with all other character data.

Numerical data is compatible with other numerical data regardless of specific data type (integer, decimal or float). Rules for operations involving mixed numerical data types are described in Comparisons.

Datetime and interval data types can be combined in arithmetic operations, for details, see Datetime and Interval Arithmetic.

Values stored in host variables (but not literals or column values) may be converted between character and numerical data types if required by the operation using the variable. The declared type of the variable itself is not altered.

Similarly, character columns may be assigned to numerical variables and vice versa. The rules for data type conversion are given below.

Variables may be converted between different data types by using the CAST function.

Datetime and Interval Arithmetic

The following table lists the arithmetic operations that are permitted involving DATE, TIME, TIMESTAMP ("DATETIME") or INTERVAL values:

Operand 1	Operator	Operand 2	Result Type
DATETIME	-	DATETIME	*See discussion below.
DATETIME	+ or -	INTERVAL	DATETIME
INTERVAL	+	DATETIME	DATETIME
INTERVAL	+ or -	INTERVAL	INTERVAL
INTERVAL	* or /	Numeric	INTERVAL
Numeric	*	INTERVAL	INTERVAL

Operands cannot be combined arithmetically unless their data types are comparable, see Comparisons. If either operand is the NULL value, then the result will always be the NULL value.

If an arithmetic operation involves two DATETIME or INTERVAL values with a defined scale, the scale of the result will be the larger of the scales of the two operands.

When an INTERVAL value is multiplied by a numeric value, the scale of the result is equal to that of the INTERVAL and the precision of the result is the leading precision of the INTERVAL increased by 1. In case of the division, the same is true except that the precision of the result is equal to the leading precision of the INTERVAL (i.e. it is not increased by 1).

When two INTERVAL values are added or subtracted, the scale (s) and precision (p) of the result are described by the following rule:

 p = min(MLP, max(p'-s', p"-s") + max(s', s") + 1)
 s = max(s', s")
 

where MLP is the maximum permitted leading precision for the INTERVAL type of the result, refer to the table in Interval for these values.

The interval precision of the result is the combined interval precision of the two operands, e.g.

 DAY TO HOUR + MINUTE TO SECOND
 

will produce a DAY TO SECOND result.

One DATETIME value may be subtracted from another to produce an INTERVAL that is the signed difference between the stated dates or times.

The application must, however, specify an INTERVAL date type for the result by using an interval-qualifier.

Thus, the syntax is:

 (DATETIME1 - DATETIME2) interval-qualifier
 

Example:

 (DATE '1996-01-09' - DATE '1996-01-01') DAY
 

This, therefore, evaluates to INTERVAL '8' DAY.

Data Types for Parameter Markers

Parameter markers in statements submitted to dynamic SQL are assigned data types appropriate to their usage. See the Mimer SQL Programmer's Manual for a discussion of dynamic SQL.

For parameter markers used to represent numerical data in arithmetic or comparison expressions, precision 45 is used.

For parameter markers used to represent data assigned to columns, the precision is assigned in accordance with the column definition.

Host Variable Data Type Conversion

When a host variable is used in assignments, comparisons or expressions where the data type of the variable is different from the data type of literals or column declarations, an attempt is made internally to convert the value of the variable to the appropriate type.

Conversion between a fixed length character variable and a VARCHAR value is always allowed. The conversion follows these rules:

• When assigning a VARCHAR value to a character variable, where the variable is longer than the VARCHAR value, the variable is padded with trailing blanks.
• When assigning a VARCHAR value to a character variable, where the value is longer than the variable, the value is truncated and a warning status returned. If only blanks are truncated, no warning is returned.
• When assigning a VARCHAR column from a character variable the column is padded with blanks up to the length of the character variable, if the VARCHAR column is longer than the variable.
• When assigning a VARCHAR column from a character variable, where the VARCHAR column is shorter than the variable (except for trailing spaces), the assignment will fail and an error status is issued.

Numerical values may always be converted to character strings, provided that the character string variable is sufficiently long enough. The resulting string format is illustrated below, using "n" to represent the appropriate number of digits and "s" to represent the sign position (blank for positive values and a minus sign for negative values).

Three digits are always used for the exponent derived from floating point numbers, regardless of the value of the exponent. The sign of the exponent is always given explicitly (+ or -).

Numerical data	String length	String format
Integer numerical precision p	p+1	'sn'
Exact numerical precision p, scale s	p+2	'sn.n'
Approximate numerical precision p	p+7	'sn.nEsn'

Note: Decimal values with scale 0 are converted to strings with the format 'sn.'. Decimal values where the scale is equal to the precision result in strings with the format 's.n'.

Examples of Assignment Results

Value	Type	Character value
1342	INTEGER(6)	'1342'
-15	INTEGER(2)	'-15'
13.42	DECIMAL(6,4)	'13.4200'
-13.	DECIMAL(5,0)	'-13.'
.13	DECIMAL(2,2)	'.13'
-1.3E56	FLOAT(2)	'-1.3E+056'

Only numerical character strings can be converted to numerical data.

Numerical strings are defined as follows:

• Integer

  One optional sign character (+ or -) followed by at least one digit (0-9). Leading and trailing blanks are ignored. No other character is allowed.

• Decimal

  As integer, but with one decimal point (.) placed immediately before or after a digit.

• Float

  As decimal, but followed directly by an uppercase or lowercase letter "E" and an exponent written as a signed integer.

The precision and scale of a number derived from a numerical character string follows the format of the string.

Leading and trailing zeros are significant for assigning precision.

Thus:

"3"	gives	INTEGER(1)
"003"	gives	INTEGER(3)
"0.3"	gives	DECIMAL(2,1)
"00.30"	gives	DECIMAL(4,2)
".3"	gives	DECIMAL(1,1)
"-33"	gives	INTEGER(2)
"-33."	gives	DECIMAL(2,0)
"003.3E14"	gives	FLOAT(4)

Standard Compliance

This section summarizes standard compliance concerning data types.

Standard	Compliance	Comments
X/Open-95 SQL92	EXTENDED	Conversion between character and numeric when storing values from or retrieving values into host variables is a Mimer SQL extension. Support for the abbreviation NUM is a Mimer SQL extension. Specifying a precision for INTEGER is a Mimer SQL extension. Support for BINARY, BINARY VARYING and BIGINT is a Mimer SQL extension.

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