Enhanced Date and Time Type Behavior with Previous SQL Server Versions (ODBC)

 

System_CAPS_ICON_warning.jpg Warning

SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

This topic describes the expected behavior when a client application that uses enhanced date and time features communicates with a version of SQL Server earlier than SQL Server 2008, and when a client application using Microsoft Data Access Components, Windows Data Access Components, or a version of SQL Server Native Client earlier than SQL Server 2008 sends commands to a server that supports enhanced date and time features.

Client applications that were compiled using a version of SQL Server Native Client prior to SQL Server 2008 see the new date/time types as nvarchar columns. The column contents are the literal representations, as described in "Data Formats: Strings and Literals" section of Data Type Support for ODBC Date and Time Improvements. The column size is the maximum literal length for the fractional seconds precision specified for the column.

Catalog APIs will return metadata consistent with the down-level data type code returned to the client (for example, nvarchar) and the associated down-level representation (for example, the appropriate literal format). However, the data type name returned will be the real SQL Server 2008 type name.

Statement metadata returned by SQLDescribeCol, SQLDescribeParam, SQGetDescField, and SQLColAttribute will return metadata that is consistent with the down-level type in all respects, including the type name. An example of such a down-level type is nvarchar.

When a down-level client application runs against a SQL Server 2008 (or later) server on which schema changes to date/time types have been made, the expected behavior is as follows:

SQL Server 2005 typeSQL Server 2008 (or later) TypeODBC client typeResult conversion (SQL to C)Parameter conversion (C to SQL)
DatetimeDateSQL_C_TYPE_DATEOKOK (1)
SQL_C_TYPE_TIMESTAMPTime fields set to zero.OK (2)

Fails if time field is non-zero. Works with SQL Server 2005.
Time(0)SQL_C_TYPE_TIMEOKOK (1)
SQL_C_TYPE_TIMESTAMPDate fields set to current date.OK (2)

Date ignored. Fails if fractional seconds are non-zero. Works with SQL Server 2005.
Time(7)SQL_C_TIMEFails – invalid time literal.OK (1)
SQL_C_TYPE_TIMESTAMPFails – invalid time literal.OK (1)
Datetime2(3)SQL_C_TYPE_TIMESTAMPOKOK (1)
Datetime2(7)SQL_C_TYPE_TIMESTAMPOKValue will be rounded to 1/300th second by client conversion.
SmalldatetimeDateSQL_C_TYPE_DATEOKOK
SQL_C_TYPE_TIMESTAMPTime fields set to zero.OK (2)

Fails if time field is non-zero. Works with SQL Server 2005.
Time(0)SQL_C_TYPE_TIMEOKOK
SQL_C_TYPE_TIMESTAMPDate fields set to current date.OK (2)

Date ignored. Fails if fractional seconds non-zero.

Works with SQL Server 2005.
Datetime2(0)SQL_C_TYPE_TIMESTAMPOKOK
SymbolMeaning
1If it worked with SQL Server 2005 it should continue to work with a more recent version of SQL Server.
2An application that worked with SQL Server 2005 could fail with a more recent version of SQL Server.

Note that only common schema changes have been considered. The following are common changes:

  • Using a new type where logically an application requires only a date or time value. However, the application was forced to use datetime or smalldatetime due to the lack of separate date and time types.

  • Using a new type to gain additional fractional seconds precision or accuracy.

  • Switching to datetime2 because this is the preferred date and time datatype.

Column Metadata Returned by SQLColumns, SQLProcedureColumns, and SQLSpecialColumns

The following column values are returned for date/time types:

Column Typedatetimesmalldatetimedatetimedatetime2datetimeoffset
DATA_TYPESQL_WVARCHARSQL_WVARCHARSQL_TYPE_TIMESTAMPSQL_TYPE_TIMESTAMPSQL_WVARCHARSQL_WVARCHAR
TYPE_NAMEdatetimesmalldatetimedatetimedatetime2datetimeoffset
COLUMN_SIZE108,10..16162319, 21..2726, 28..34
BUFFER_LENGTH2016, 20..32161638, 42..5452, 56..68
DECIMAL_DIGITSNULLNULL03NULLNULL
SQL_DATA_TYPESQL_WVARCHARSQL_WVARCHARSQL_DATETIMESQL_DATETIMESQL_WVARCHARSQL_WVARCHAR
SQL_DATETIME_SUBNULLNULLSQL_CODE_TIMESTAMPSQL_CODE_TIMESTAMPNULLNULL
CHAR_OCTET_LENGTHNULLNULLNULLNULLNULLNULL
SS_DATA_TYPE0011111100

SQLSpecialColumns does not return SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH, or SS_DATA_TYPE.

Data Type Metadata Returned by SQLGetTypeInfo

The following column values are returned for date/time types:

Column Typedatetimesmalldatetimedatetimedatetime2datetimeoffset
TYPE_NAMEdatetimesmalldatetimedatetimedatetime2datetimeoffset
DATA_TYPESQL_WVARCHARSQL_WVARCHARSQL_TYPE_TIMESTAMPSQL_TYPE_TIMESTAMPSQL_WVARCHARSQL_WVARCHAR
COLUMN_SIZE101616232734
LITERAL_PREFIX
LITERAL_SUFFIX
CREATE_PARAMSNULLNULLNULLNULLNULLNULL
NULLABLESQL_NULLABLESQL_NULLABLESQL_NULLABLESQL_NULLABLESQL_NULLABLESQL_NULLABLE
CASE_SENSITIVESQL_FALSESQL_FALSESQL_FALSESQL_FALSESQL_FALSESQL_FALSE
SEARCHABLESQL_PRED_SEARCHABLESQL_PRED_SEARCHABLESQL_PRED_SEARCHABLESQL_PRED_SEARCHABLESQL_PRED_SEARCHABLESQL_PRED_SEARCHABLE
UNSIGNED_ATTRIBUTENULLNULLNULLNULLNULLNULL
FXED_PREC_SCALESQL_FALSESQL_FALSESQL_FALSESQL_FALSESQL_FALSESQL_FALSE
AUTO_UNIQUE_VALUENULLNULLNULLNULLNULLNULL
LOCAL_TYPE_NAMEdatetimesmalldatetimedatetimedatetime2datetimeoffset
MINIMUM_SCALENULLNULL03NULLNULL
MAXIMUM_SCALENULLNULL03NULLNULL
SQL_DATA_TYPESQL_WVARCHARSQL_WVARCHARSQL_DATETIMESQL_DATETIMESQL_WVARCHARSQL_WVARCHAR
SQL_DATETIME_SUBNULLNULLSQL_CODE_TIMESTAMPSQL_CODE_TIMESTAMPNULLNULL
NUM_PREC_RADIXNULLNULLNULLNULLNULLNULL
INTERVAL_PRECISIONNULLNULLNULLNULLNULLNULL
USERTYPE00122200

When connected to a server instance of an earlier version that SQL Server 2008, any attempt to use the new server types or associated metadata codes and descriptor fields will result in SQL_ERROR being returned. A diagnostic record will be generated with SQLSTATE HY004 and the message "Invalid SQL data type for server version on connection", or with 07006 and "Restricted data type attribute violation".

Date and Time Improvements (ODBC)

Community Additions

ADD
Show: