Datetime Data Types

In ODBC 3.0, the identifiers for date, time, and timestamp SQL data types have changed from SQL_DATE, SQL_TIME, and SQL_TIMESTAMP (with #defines in the header file of 9, 10, and 11) to SQL_TYPE_DATE, SQL_TYPE_TIME, and SQL_TYPE_TIMESTAMP (with #defines in the header file of 91, 92, and 93), respectively. The corresponding C type identifiers have changed from SQL_C_DATE, SQL_C_TIME, and SQL_C_TIMESTAMP to SQL_C_TYPE_DATE, SQL_C_TYPE_TIME, and SQL_C_TYPE_TIMESTAMP, respectively, and the #defines have changed accordingly.

The column size and decimal digits returned for the SQL datetime data types in ODBC 3.0 are the same as the precision and scale returned for them in ODBC 2.x. These values are different than the values in the SQL_DESC_PRECISION and SQL_DESC_SCALE descriptor fields. (For more information, see Appendix D, “Data Types.”)

These changes affect SQLDescribeCol, SQLDescribeParam, and SQLColAttributes; SQLBindCol, SQLBindParameter, and SQLGetData; and SQLColumns, SQLGetTypeInfo, SQLProcedureColumns, SQLStatistics, and SQLSpecialColumns.

An ODBC 3.0 driver processes the function calls listed in the previous paragraph according to the setting of the SQL_ATTR_ODBC_VERSION environment attribute. For SQLColumns, SQLGetTypeInfo, SQLProcedureColumns, SQLSpecialColumns, and SQLStatistics, if SQL_ATTR_ODBC_VERSION is set to SQL_OV_ODBC3, the functions return SQL_TYPE_DATE, SQL_TYPE_TIME, and SQL_TYPE_TIMESTAMP in the DATA_TYPE field. The COLUMN_SIZE column (in the result set returned by SQLColumns, SQLGetTypeInfo, SQLProcedureColumns, and SQLSpecialColumns) contains the binary precision for the approximate numeric type. The NUM_PREC_RADIX column (in the result set returned by SQLColumns, SQLGetTypeInfo, and SQLProcedureColumns), contains a value of 2. If SQL_ATTR_ODBC_VERSION is set to SQL_OV_ODBC2, the functions return SQL_DATE, SQL_TIME, and SQL_TIMESTAMP in the DATA_TYPE field, the COLUMN_SIZE column contains the decimal precision for the approximate numeric type, and the NUM_PREC_RADIX column contains a value of 10.

When all data types are requested in a call to SQLGetTypeInfo, the result set returned by the function will contain both SQL_TYPE_DATE, SQL_TYPE_TIME, and SQL_TYPE_TIMESTAMP as defined in ODBC 3.0, and SQL_DATE, SQL_TIME, and SQL_TIMESTAMP as defined in ODBC 2.x.

Because of how the ODBC 3.0 Driver Manager performs mapping of the date, time, and timestamp data types, ODBC 3.0 drivers need only recognize #defines of 91, 92, and 93 for the date, time, and timestamp C data types entered in the TargetType arguments of SQLBindCol and SQLGetData or the ValueType argument of SQLBindParameter, and need only recognize #defines of 91, 92, and 93 for the date, time, and timestamp SQL data types entered in the ParameterType argument of SQLBindParameter or the DataType argument of SQLGetTypeInfo. For more information, see “Datetime Data Type Changes” in Chapter 17, “Programming Considerations.”