SQL Data Types

Each DBMS defines its own SQL types. Each ODBC driver exposes only those SQL data types that the associated DBMS defines. How a driver maps DBMS SQL types to the ODBC-defined SQL type identifiers, and how a driver maps DBMS SQL types to its own driver-specific SQL type identifiers, are returned through a call to SQLGetTypeInfo. A driver also returns the SQL data types when describing the data types of columns and parameters through calls to SQLColAttribute, SQLColumns, SQLDescribeCol, SQLDescribeParam, SQLProcedureColumns, and SQLSpecialColumns.

Note The SQL data types are contained in the SQL_DESC_ CONCISE_TYPE, SQL_DESC_TYPE, and SQL_DESC_DATETIME_INTERVAL_CODE fields of the implementation descriptors. Characteristics of the SQL data types are contained in the SQL_DESC_PRECISION, SQL_DESC_SCALE, SQL_DESC_LENGTH, and SQL_DESC_OCTET_LENGTH fields of the implementation descriptors. For more information, see “Data Type Identifiers and Descriptors” later in this appendix.

A given driver and data source do not necessarily support all of the SQL data types defined in this appendix. A driver’s support for SQL data types depends upon the level of SQL-92 that the driver conforms to. To determine the level of SQL-92 grammar supported by the driver, an application calls SQLGetInfo with the SQL_SQL_CONFORMANCE information type. Furthermore, a given driver and data source may support additional, driver-specific SQL data types. To determine which data types a driver supports, an application calls SQLGetTypeInfo. For information about driver-specific SQL data types, see the driver’s documentation. For information about the data types in a specific data source, see the documentation for that data source.

Important The tables throughout this appendix are only a guideline and show commonly used names, ranges, and limits of SQL data types. A given data source may support only some of the listed data types and the characteristics of the supported data types may differ from those listed.

The following table lists valid SQL type identifiers for all SQL data types. The table also lists the name and description of the corresponding data type from SQL-92 (if one exists).

SQL type identifier [1]
Typical SQL data type [2]
Typical type description

SQL_CHAR
CHAR(n)
Character string of fixed string length n.

SQL_VARCHAR
VARCHAR(n)
Variable-length character string with a maximum string length n.

SQL_LONGVARCHAR
LONG VARCHAR
Variable length character data. Maximum length is data source–dependent. [9]

SQL_DECIMAL
DECIMAL(p,s)
Signed, exact, numeric value with a precision of at least p and scale s. (The maximum precision is driver-defined.)
(1 <= p <= 15; s <= p). [4]

SQL_NUMERIC
NUMERIC(p,s)
Signed, exact, numeric value with a precision p and scale s
(1 <= p <= 15; s <= p). [4]

SQL_SMALLINT
SMALLINT
Exact numeric value with precision 5 and scale 0 (signed: –32,768 <= n <= 32,767, unsigned:
0<= n <= 65,535) [3] .

SQL_INTEGER
INTEGER
Exact numeric value with precision 10 and scale 0 (signed: –2[31] <= n <= 2[31] – 1, unsigned: 0 <= n <= 2[32] – 1) [3] .

SQL_REAL
REAL
Signed, approximate, numeric value with a binary precision 24 (zero or absolute value 10[–38] to 10[38]).

SQL_FLOAT
FLOAT(p)
Signed, approximate, numeric value with a binary precision of at least p. (The maximum precision is driver-defined.) [5]

SQL_DOUBLE
DOUBLE
PRECISION
Signed, approximate, numeric value with a binary precision 53 (zero or absolute value 10[–308] to 10[308]).

SQL_BIT
BIT
Single bit binary data. [8]

SQL_TINYINT
TINYINT
Exact numeric value with precision 3 and scale 0 (signed: –128 <= n <= 127, unsigned: 0<= n <= 255) [3] .

SQL_BIGINT
BIGINT
Exact numeric value with precision 19 (if signed) or 20 (if unsigned) and scale 0 (signed: –2[63] <= n <= 2[63] – 1, unsigned: 0 <= n <= 2[64] – 1) [3], [9].

SQL_BINARY
BINARY(n)
Binary data of fixed length n. [9]

SQL_VARBINARY
VARBINARY(n)
Variable length binary data of maximum length n. The maximum is set by the user. [9]

SQL_LONGVARBINARY
LONG VARBINARY
Variable length binary data. Maximum length is data source–dependent. [9]

SQL_TYPE_DATE [6]
DATE
Year, month, and day fields, conforming to the rules of the Gregorian calendar (see “Constraints of the Gregorian Calendar” later in this appendix).

SQL_TYPE_TIME [6]
TIME(p)
Hour, minute, and second fields, with valid values for hours of 00 to 23, valid values for minutes of 00 to 59, and valid values for seconds of 00 to 61. Precision p indicates the seconds precision.

SQL_TYPE_TIMESTAMP [6]
TIMESTAMP(p)
Year, month, day, hour, minute, and second fields, with valid values as defined for the DATE and TIME data types.

SQL_INTERVAL_
MONTH [7]
INTERVAL MONTH(p)
Number of months between two dates; p is the interval leading precision.
SQL_INTERVAL_YEAR [7]
INTERVAL YEAR(p)
Number of years between two dates; p is the interval leading precision.
SQL_INTERVAL_YEAR_
TO_MONTH [7]
INTERVAL YEAR(p) TO MONTH
Number of years and months between two dates; p is the interval leading precision.
SQL_INTERVAL_DAY [7]
INTERVAL DAY(p)
Number of days between two dates; p is the interval leading precision.
SQL_INTERVAL_HOUR [7]
INTERVAL HOUR(p)
Number of hours between two date/times; p is the interval leading precision.
SQL_INTERVAL_
MINUTE [7]
INTERVAL MINUTE(p)
Number of minutes between two date/times; p is the interval leading precision.
SQL_INTERVAL_
SECOND [7]
INTERVAL SECOND(p,q)
Number of seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
SQL_INTERVAL_DAY_
TO_HOUR [7]
INTERVAL DAY(p) TO HOUR
Number of days/hours between two date/times; p is the interval leading precision.
SQL_INTERVAL_DAY_
TO_MINUTE [7]
INTERVAL DAY(p) TO MINUTE
Number of days/hours/minutes between two date/times; p is the interval leading precision.
SQL_INTERVAL_DAY_
TO_SECOND [7]
INTERVAL DAY(p) TO SECOND(q)
Number of days/hours/minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
SQL_INTERVAL_HOUR_
TO_MINUTE [7]
INTERVAL HOUR(p) TO MINUTE
Number of hours/minutes between two date/times; p is the interval leading precision.
SQL_INTERVAL_HOUR_
TO_SECOND [7]
INTERVAL HOUR(p) TO SECOND(q)
Number of hours/minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
SQL_INTERVAL_
MINUTE_TO_SECOND [7]
INTERVAL MINUTE(p) TO SECOND(q)
Number of minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.

[1] This is the value returned in the DATA_TYPE column by a call to SQLGetTypeInfo.
[2] This is the value returned in the NAME and CREATE PARAMS column by a call to SQLGetTypeInfo. The NAME column returns the designation; for example, CHAR, while the CREATE PARAMS column returns a comma-separated list of creation parameters such as precision, scale, and length.
[3] An application uses SQLGetTypeInfo or SQLColAttribute to determine if a particular data type or a particular column in a result set is unsigned.
[4] SQL_DECIMAL and SQL_NUMERIC data types differ only in their precision. The precision of a DECIMAL(p,s) is an implementation-defined decimal precision that is no less than p, while the precision of a NUMERIC(p,s) is exactly equal to p.
[5] Depending on the implementation, the precision of SQL_FLOAT can be either 24 or 53: if it is 24, the SQL_FLOAT data type is the same as SQL_REAL, if it is 53, the SQL_FLOAT data type is the same as SQL_DOUBLE.
[6] In ODBC 3.0, the SQL date, time, and timestamp data types are SQL_TYPE_DATE, SQL_TYPE_TIME, and SQL_TYPE_TIMESTAMP, respectively; in ODBC 2.x, the data types are SQL_DATE, SQL_TIME, and SQL_TIMESTAMP.
[7] For more information on the interval SQL data types, see the “Interval Data Types” section later in this appendix.
[8] The SQL_BIT data type has different characteristics than the BIT type in SQL-92.
[9] This data type has no corresponding data type in SQL-92.

Example SQLGetTypeInfo Result Set

An application calls SQLGetTypeInfo to determine which data types are supported by a data source and the characteristics of those data types. The following tables show a sample result set returned by SQLGetTypeInfo for a data source that supports SQL_CHAR, SQL_LONGVARCHAR, SQL_DECIMAL, SQL_REAL, SQL_DATETIME, SQL_INTERVAL_YEAR, and SQL_INTERVAL_DAY_MONTH.

TYPE_
NAME
DATA_
TYPE
COLUMN_
SIZE
LITERAL_
PREFIX
LITERAL_
SUFFIX
CREATE_
PARAMS
NULL-
ABLE

"char"
SQL_CHAR
255
"'"
"'"
"length"
SQL_
TRUE
"text"
SQL_LONG
VARCHAR
2147483647
"'"
"'"
<Null>
SQL_
TRUE
"decimal"
SQL_
DECIMAL
28
<Null>
<Null>
"precision,
scale"
SQL_
TRUE
"real"
SQL_
REAL
7
<Null>
<Null>
<Null>
SQL_
TRUE
"datetime"
SQL_TYPE_
TIMESTAMP
23
"'"
"'"
<Null>
SQL_
TRUE
“INTER-
VAL YEAR() TO YEAR”
SQL_
INTERVAL_
YEAR
9
"'"
"'"
“precision”
SQL_
TRUE
“INTER-
VAL DAY() TO FRAC-
TION(5)”
SQL_
INTERVAL_
DAY_TO_
SECOND
24
"'"
"'"
“precision”
SQL_
TRUE

CASE_
SENSI-
TIVE
SEARCH-
ABLE
UNSIGNED_
ATTRIBUTE
FIXED_
PREC_
SCALE
AUTO_
UNIQUE_
VALUE
LOCAL_
TYPE_
NAME

SQL_CHAR
SQL_
FALSE
SQL_
SEARCH-
ABLE
<Null>
SQL_
FALSE
<Null>
"char"
SQL_LONG
VARCHAR
SQL_
FALSE
SQL_
PRED_
CHAR
<Null>
SQL_
FALSE
<Null>
"text"
SQL_
DECIMAL
SQL_
FALSE
SQL_
PRED_
BASIC
SQL_
FALSE
SQL_
FALSE
SQL_
FALSE
"decimal"
SQL_
REAL
SQL_
FALSE
SQL_
PRED_
BASIC
SQL_
FALSE
SQL_
FALSE
SQL_
FALSE
"real"
SQL_TYPE_
TIMESTAMP
SQL_
FALSE
SQL_
SEARCH-
ABLE
<Null>
SQL_
FALSE
<Null>
"datetime"
SQL_
INTERVAL_
YEAR
SQL_
FALSE
SQL_
SEARCH-
ABLE
<Null>
SQL_
FALSE
<Null>
“INTER-
VAL YEAR() TO YEAR”
SQL_
INTERVAL_
DAY_TO_
SECOND
SQL_
FALSE
SQL_
PRED_
BASIC
<Null>
SQL_
FALSE
<Null>
“INTER-
VAL DAY() TO FRAC-
TION(5)”

MIN-
IMUM_
SCALE
MAX-
IMUM_
SCALE
SQL_
DATA_
TYPE
SQL_
DATE-
TIME_
SUB
NUM_
PREC_
RADIX
INTER-
VAL_PRE-
CISION

SQL_CHAR
<Null>
<Null>
SQL_
CHAR
<Null>
<Null>
<Null>
SQL_LONG
VARCHAR
<Null>
<Null>
SQL_
LONG-
VARCHAR
<Null>
<Null>
<Null>
SQL_
DECIMAL
0
28
SQL_
DECIMAL
<Null>
10
<Null>
SQL_
REAL
<Null>
<Null>
SQL_
REAL
<Null>
10
<Null>
SQL_
TYPE_
TIME-
STAMP
3
3
SQL_
DATE-
TIME
SQL_
CODE_
TIME-
STAMP
<Null>
12
SQL_
INTERVAL_
YEAR
0
0
SQL_
INTERVAL
SQL_
CODE_
INTERVAL
YEAR
<Null>
9
SQL_
INTERVAL_
DAY_TO_
SECOND
5
5
SQL_
INTERVAL
SQL_
CODE_
INTERVAL
DAY_TO_
SECOND
<Null>
9

SQL_INTERVAL_ MONTH [7]	INTERVAL MONTH(p)	Number of months between two dates; p is the interval leading precision.
SQL_INTERVAL_YEAR [7]	INTERVAL YEAR(p)	Number of years between two dates; p is the interval leading precision.
SQL_INTERVAL_YEAR_ TO_MONTH [7]	INTERVAL YEAR(p) TO MONTH	Number of years and months between two dates; p is the interval leading precision.
SQL_INTERVAL_DAY [7]	INTERVAL DAY(p)	Number of days between two dates; p is the interval leading precision.
SQL_INTERVAL_HOUR [7]	INTERVAL HOUR(p)	Number of hours between two date/times; p is the interval leading precision.
SQL_INTERVAL_ MINUTE [7]	INTERVAL MINUTE(p)	Number of minutes between two date/times; p is the interval leading precision.
SQL_INTERVAL_ SECOND [7]	INTERVAL SECOND(p,q)	Number of seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
SQL_INTERVAL_DAY_ TO_HOUR [7]	INTERVAL DAY(p) TO HOUR	Number of days/hours between two date/times; p is the interval leading precision.
SQL_INTERVAL_DAY_ TO_MINUTE [7]	INTERVAL DAY(p) TO MINUTE	Number of days/hours/minutes between two date/times; p is the interval leading precision.
SQL_INTERVAL_DAY_ TO_SECOND [7]	INTERVAL DAY(p) TO SECOND(q)	Number of days/hours/minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
SQL_INTERVAL_HOUR_ TO_MINUTE [7]	INTERVAL HOUR(p) TO MINUTE	Number of hours/minutes between two date/times; p is the interval leading precision.
SQL_INTERVAL_HOUR_ TO_SECOND [7]	INTERVAL HOUR(p) TO SECOND(q)	Number of hours/minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.
SQL_INTERVAL_ MINUTE_TO_SECOND [7]	INTERVAL MINUTE(p) TO SECOND(q)	Number of minutes/seconds between two date/times; p is the interval leading precision and q is the interval seconds precision.

"char"	SQL_CHAR	255	"'"	"'"	"length"	SQL_ TRUE
"text"	SQL_LONG VARCHAR	2147483647	"'"	"'"	<Null>	SQL_ TRUE
"decimal"	SQL_ DECIMAL	28	<Null>	<Null>	"precision, scale"	SQL_ TRUE
"real"	SQL_ REAL	7	<Null>	<Null>	<Null>	SQL_ TRUE
"datetime"	SQL_TYPE_ TIMESTAMP	23	"'"	"'"	<Null>	SQL_ TRUE
“INTER- VAL YEAR() TO YEAR”	SQL_ INTERVAL_ YEAR	9	"'"	"'"	“precision”	SQL_ TRUE
“INTER- VAL DAY() TO FRAC- TION(5)”	SQL_ INTERVAL_ DAY_TO_ SECOND	24	"'"	"'"	“precision”	SQL_ TRUE

SQL_CHAR	SQL_ FALSE	SQL_ SEARCH- ABLE	<Null>	SQL_ FALSE	<Null>	"char"
SQL_LONG VARCHAR	SQL_ FALSE	SQL_ PRED_ CHAR	<Null>	SQL_ FALSE	<Null>	"text"
SQL_ DECIMAL	SQL_ FALSE	SQL_ PRED_ BASIC	SQL_ FALSE	SQL_ FALSE	SQL_ FALSE	"decimal"
SQL_ REAL	SQL_ FALSE	SQL_ PRED_ BASIC	SQL_ FALSE	SQL_ FALSE	SQL_ FALSE	"real"
SQL_TYPE_ TIMESTAMP	SQL_ FALSE	SQL_ SEARCH- ABLE	<Null>	SQL_ FALSE	<Null>	"datetime"
SQL_ INTERVAL_ YEAR	SQL_ FALSE	SQL_ SEARCH- ABLE	<Null>	SQL_ FALSE	<Null>	“INTER- VAL YEAR() TO YEAR”
SQL_ INTERVAL_ DAY_TO_ SECOND	SQL_ FALSE	SQL_ PRED_ BASIC	<Null>	SQL_ FALSE	<Null>	“INTER- VAL DAY() TO FRAC- TION(5)”