Datetime Data Type Changes

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.

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 SQLColAttribute; SQLBindCol, SQLBindParameter, and SQLGetData; and SQLColumns, SQLGetTypeInfo, SQLProcedureColumns, SQLStatistics, and SQLSpecialColumns.

The following table shows how the ODBC 3.0 Driver Manager performs mapping of the date, time, and timestamp C data types entered in the TargetType arguments of SQLBindCol and SQLGetData or the ValueType argument of SQLBindParameter.

Data type code entered

2.x app to 2.x driver

2.x app to 3.0 driver

3.0 app to 2.x driver

3.0 app to 3.0 driver

SQL_C_DATE (9)	No mapping	SQL_C_ TYPE_DATE (91)	No mapping [1]	SQL_C_ TYPE_DATE (91)
SQL_C_ TYPE_DATE (91)	Error (from DM)	Error (from DM)	SQL_C_DATE (9)	No mapping [2]
SQL_C_TIME (10)	No mapping	SQL_C_ TYPE_TIME (92)	No mapping [1]	SQL_C_ TYPE_TIME (92)
SQL_C_ TYPE_TIME (92)	Error (from DM)	Error (from DM)	SQL_C_TIME (10)	No mapping [2]
SQL_C_ TIMESTAMP (11)	No mapping	SQL_C_ TYPE_ TIMESTAMP (93)	No mapping [1]	SQL_C_ TYPE_ TIMESTAMP (93)
SQL_C_ TYPE_ TIMESTAMP (93)	Error (from DM)	Error (from DM)	SQL_C_ TIMESTAMP (11)	No mapping [2]

[1] As a result of this, an ODBC 3.0 application working with an ODBC 2.x driver can use the date, time, or timestamp codes returned in the result sets that are returned by the catalog functions. [2] As a result of this, an ODBC 3.0 application working with an ODBC 3.0 driver can use the date, time, or timestamp codes returned in the result sets that are returned by the catalog functions.

The following table shows how the ODBC 3.0 Driver Manager performs mapping of the date, time, and timestamp SQL data types entered in the ParameterType argument of SQLBindParameter or the DataType argument of SQLGetTypeInfo.

Data type code entered	2.x app to 2.x driver	2.x app to 3.0 driver	3.0 app to 2.x driver	3.0 app to 3.0 driver
SQL_DATE (9)	No mapping	SQL_TYPE_ DATE (91)	No mapping [1]	SQL_TYPE_ DATE (91)
SQL_TYPE_ DATE (91)	Error (from DM)	Error (from DM)	SQL_DATE (9)	No mapping [2]
SQL_TIME (10)	No mapping	SQL_TYPE_ TIME (92)	No mapping [1]	SQL_TYPE_ TIME (92)
SQL_TYPE_ TIME (92)	Error (from DM)	Error (from DM)	SQL_TIME (10)	No mapping [2]
SQL_ TIMESTAMP (11)	No mapping	SQL_TYPE_ TIMESTAMP (93)	No mapping [1]	SQL_TYPE_ TIMESTAMP (93)
SQL_TYPE_ TIMESTAMP (93)	Error (from DM)	Error (from DM)	SQL_ TIMESTAMP (11)	No mapping [2]

[1] As a result of this, an ODBC 3.0 application working with an ODBC 2.x driver can used the date, time, or timestamp codes returned in the result sets that are returned by the catalog functions. [2] As a result of this, an ODBC 3.0 application working with an ODBC 3.0 driver can used the date, time, or timestamp codes returned in the result sets that are returned by the catalog functions.