SQL Data Types
Each DBMS defines its own SQL types. Each ODBC driver exposes only those SQL data types that the associated DBMS defines. Information about 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 is 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 the SQL data types that are defined in this appendix. A driver's support for SQL data types depends on the level of SQL-92 that the driver complies with. To determine the level of SQL-92 grammar supported by the driver, an application calls SQLGetInfo with the SQL_SQL_CONFORMANCE information type. Additionally, 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 guidelines and show typically used names, ranges, and limits of SQL data types. A given data source might support only some of the listed data types, and the characteristics of the supported data types can 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_WCHAR | WCHAR(n) | Unicode character string of fixed string length n |
| SQL_WVARCHAR | VARWCHAR(n) | Unicode variable-length character string with a maximum string length n |
| SQL_WLONGVARCHAR | LONGWVARCHAR | Unicode variable-length character data. Maximum length is data source-dependent |
| 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_TYPE_UTCDATETIME | UTCDATETIME | Year, month, day, hour, minute, second, utchour, and utcminute fields. The utchour and utcminute fields have 1/10 microsecond precision. |
| SQL_TYPE_UTCTIME | UTCTIME | Hour, minute, second, utchour, and utcminute fields. The utchour and utcminute fields have 1/10 microsecond precision.. |
| 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. |
| SQL_GUID | GUID | Fixed length GUID. |
[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-whereas 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 whether 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, whereas 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.x, 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 about 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.
This section provides the following example.