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

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

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.

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.

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.

Datetime2(0)

SQL_C_TYPE_TIMESTAMP

OK

OK

Key to Symbols

Symbol

Meaning

1

If it worked with SQL Server 2005 it should continue to work with a more recent version of SQL Server.

2

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

Concepts

Date/Time Improvements (ODBC)