# 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( | Character string of fixed string length |

SQL_VARCHAR | VARCHAR( | Variable-length character string with a maximum string length |

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

SQL_WCHAR | WCHAR( | Unicode character string of fixed string length |

SQL_WVARCHAR | VARWCHAR( | Unicode variable-length character string with a maximum string length |

SQL_WLONGVARCHAR | LONGWVARCHAR | Unicode variable-length character data. Maximum length is data source–dependent |

SQL_DECIMAL | DECIMAL( | Signed, exact, numeric value with a precision of at least |

SQL_NUMERIC | NUMERIC( | Signed, exact, numeric value with a precision |

SQL_SMALLINT | SMALLINT | Exact numeric value with precision 5 and scale 0 (signed: –32,768 <= |

SQL_INTEGER | INTEGER | Exact numeric value with precision 10 and scale 0 (signed: –2[31] <= |

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

SQL_FLOAT | FLOAT( | Signed, approximate, numeric value with a binary precision of at least |

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 <= |

SQL_BIGINT | BIGINT | Exact numeric value with precision 19 (if signed) or 20 (if unsigned) and scale 0 (signed: –2[63] <= |

SQL_BINARY | BINARY( | Binary data of fixed length |

SQL_VARBINARY | VARBINARY( | Variable length binary data of maximum length |

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( | 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 |

SQL_TYPE_TIMESTAMP[6] | TIMESTAMP( | 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( | Number of months between two dates; |

SQL_INTERVAL_YEAR[7] | INTERVAL YEAR( | Number of years between two dates; |

SQL_INTERVAL_YEAR_TO_MONTH[7] | INTERVAL YEAR( | Number of years and months between two dates; |

SQL_INTERVAL_DAY[7] | INTERVAL DAY( | Number of days between two dates; |

SQL_INTERVAL_HOUR[7] | INTERVAL HOUR( | Number of hours between two date/times; |

SQL_INTERVAL_MINUTE[7] | INTERVAL MINUTE( | Number of minutes between two date/times; |

SQL_INTERVAL_SECOND[7] | INTERVAL SECOND( | Number of seconds between two date/times; |

SQL_INTERVAL_DAY_TO_HOUR[7] | INTERVAL DAY( | Number of days/hours between two date/times; |

SQL_INTERVAL_DAY_TO_MINUTE[7] | INTERVAL DAY( | Number of days/hours/minutes between two date/times; |

SQL_INTERVAL_DAY_TO_SECOND[7] | INTERVAL DAY( | Number of days/hours/minutes/seconds between two date/times; |

SQL_INTERVAL_HOUR_TO_MINUTE[7] | INTERVAL HOUR( | Number of hours/minutes between two date/times; |

SQL_INTERVAL_HOUR_TO_SECOND[7] | INTERVAL HOUR( | Number of hours/minutes/seconds between two date/times; |

SQL_INTERVAL_MINUTE_TO_SECOND[7] | INTERVAL MINUTE( | Number of minutes/seconds between two date/times; |

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.