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

Applies to: yesSQL Server (all supported versions) YesAzure SQL Database YesAzure SQL Managed Instance yesAzure Synapse Analytics yesParallel Data Warehouse

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.

Down-Level Client Behavior

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 type SQL Server 2008 (or later) Type ODBC client type Result conversion (SQL to C) Parameter conversion (C to SQL)
Datetime Date SQL_C_TYPE_DATE OK OK (1)
SQL_C_TYPE_TIMESTAMP Time fields set to zero. OK (2)

Fails if time field is non-zero. Works with SQL Server 2005 (9.x).
Time(0) SQL_C_TYPE_TIME OK OK (1)
SQL_C_TYPE_TIMESTAMP Date fields set to current date. OK (2)

Date ignored. Fails if fractional seconds are non-zero. Works with SQL Server 2005 (9.x).
Time(7) SQL_C_TIME Fails - invalid time literal. OK (1)
SQL_C_TYPE_TIMESTAMP Fails - invalid time literal. OK (1)
Datetime2(3) SQL_C_TYPE_TIMESTAMP OK OK (1)
Datetime2(7) SQL_C_TYPE_TIMESTAMP OK Value will be rounded to 1/300th second by client conversion.
Smalldatetime Date SQL_C_TYPE_DATE OK OK
SQL_C_TYPE_TIMESTAMP Time fields set to zero. OK (2)

Fails if time field is non-zero. Works with SQL Server 2005 (9.x).
Time(0) SQL_C_TYPE_TIME OK OK
SQL_C_TYPE_TIMESTAMP Date fields set to current date. OK (2)

Date ignored. Fails if fractional seconds non-zero.

Works with SQL Server 2005 (9.x).
Datetime2(0) SQL_C_TYPE_TIMESTAMP OK OK

Key to Symbols

Symbol Meaning
1 If it worked with SQL Server 2005 (9.x) it should continue to work with a more recent version of SQL Server.
2 An application that worked with SQL Server 2005 (9.x) 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 Type date time smalldatetime datetime datetime2 datetimeoffset
DATA_TYPE SQL_WVARCHAR SQL_WVARCHAR SQL_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMP SQL_WVARCHAR SQL_WVARCHAR
TYPE_NAME date time smalldatetime datetime datetime2 datetimeoffset
COLUMN_SIZE 10 8,10..16 16 23 19, 21..27 26, 28..34
BUFFER_LENGTH 20 16, 20..32 16 16 38, 42..54 52, 56..68
DECIMAL_DIGITS NULL NULL 0 3 NULL NULL
SQL_DATA_TYPE SQL_WVARCHAR SQL_WVARCHAR SQL_DATETIME SQL_DATETIME SQL_WVARCHAR SQL_WVARCHAR
SQL_DATETIME_SUB NULL NULL SQL_CODE_TIMESTAMP SQL_CODE_TIMESTAMP NULL NULL
CHAR_OCTET_LENGTH NULL NULL NULL NULL NULL NULL
SS_DATA_TYPE 0 0 111 111 0 0

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 Type date time smalldatetime datetime datetime2 datetimeoffset
TYPE_NAME date time smalldatetime datetime datetime2 datetimeoffset
DATA_TYPE SQL_WVARCHAR SQL_WVARCHAR SQL_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMP SQL_WVARCHAR SQL_WVARCHAR
COLUMN_SIZE 10 16 16 23 27 34
LITERAL_PREFIX ' ' ' ' ' '
LITERAL_SUFFIX ' ' ' ' ' '
CREATE_PARAMS NULL NULL NULL NULL NULL NULL
NULLABLE SQL_NULLABLE SQL_NULLABLE SQL_NULLABLE SQL_NULLABLE SQL_NULLABLE SQL_NULLABLE
CASE_SENSITIVE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE
SEARCHABLE SQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLE
UNSIGNED_ATTRIBUTE NULL NULL NULL NULL NULL NULL
FXED_PREC_SCALE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE
AUTO_UNIQUE_VALUE NULL NULL NULL NULL NULL NULL
LOCAL_TYPE_NAME date time smalldatetime datetime datetime2 datetimeoffset
MINIMUM_SCALE NULL NULL 0 3 NULL NULL
MAXIMUM_SCALE NULL NULL 0 3 NULL NULL
SQL_DATA_TYPE SQL_WVARCHAR SQL_WVARCHAR SQL_DATETIME SQL_DATETIME SQL_WVARCHAR SQL_WVARCHAR
SQL_DATETIME_SUB NULL NULL SQL_CODE_TIMESTAMP SQL_CODE_TIMESTAMP NULL NULL
NUM_PREC_RADIX NULL NULL NULL NULL NULL NULL
INTERVAL_PRECISION NULL NULL NULL NULL NULL NULL
USERTYPE 0 0 12 22 0 0

Down-Level Server Behavior

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".

See Also

Date and Time Improvements (ODBC)