New Date/Time Features with Previous SQL Server Versions (OLE DB)

Down-Level Client Behavior

Client applications that use a version of SQL Server Native Client earlier than SQL Server 2008 see the new date/time types as nvarchar columns. The column contents are literal representations. For more information, see the "Data Formats: Strings and Literals" section of Data Type Support for OLE DB Date/Time Improvements. The column size is the maximum literal length for the 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.

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:

OK means that if it worked with SQL Server 2005, it should continue to work with SQL Server 2008 (or later).

Only the following common schema changes have been considered:

• 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 because separate date and time types were not available.

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

• Switching to datetime2 because this is the preferred data type for date and time.

Applications that use server metadata obtained through ICommandWithParameters::GetParameterInfo or schema rowsets to set parameter type information through ICommandWithParameters::SetParameterInfo will fail during client conversions where the string representation of a source type is larger than the string representation of the destination type. For example, if a client binding uses DBTYPE_DBTIMESTAMP and the server column is date, SQL Server Native Client will convert the value to "yyyy-dd-mm hh:mm:ss.fff", but server metadata will be returned as nvarchar(10). The resulting overflow causes DBSTATUS_E_CATCONVERTVALUE. Similar problems arise with data conversions by IRowsetChange, because the rowset metadata is set from the resultset metadata.

Parameter and Rowset Metadata

Schema Rowsets

Down-Level Server Behavior

When connected to a server of an earlier version than SQL Server 2008, any attempt to use the new server type names (for example, with ICommandWithParameters::SetParameterInfo or ITableDefinition::CreateTable) will result in DB_E_BADTYPENAME.

If new types are bound for parameters or results without the use of a type name, and either the new type is used to specify the server type implicitly or there is no valid conversion from the server type to the client type, DB_E_ERRORSOCCURRED is returned, and DBBINDSTATUS_UNSUPPORTED_CONVERSION is set as the binding status for the accessor used at Execute.

If there is a supported client conversion from the buffer type to the server type for the server version on the connection, all client buffer types can be used. In this context, server type means the type specified by ICommandWithParameters::SetParameterInfo, or implied by the buffer type if ICommandWithParameters::SetParameterInfo has not been called. This means that DBTYPE_DBTIME2 and DBTYPE_DBTIMESTAMPOFFSET can be used with down-level servers, or when DataTypeCompatibility=80, if the client conversion to a supported server type succeeds. Of course, if the server type is incorrect, an error might still be reported by the server if it cannot perform an implicit conversion to the actual server type.

SSPROP_INIT_DATATYPECOMPATIBILITY Behavior

When SSPROP_INIT_DATATYPECOMPATIBILITY is set to SSPROPVAL_DATATYPECOMPATIBILITY_SQL2000, the new date/time types and associated metadata appear to clients as they appear for down-level clients, as described in Bulk Copy Changes for Enhanced Date/Time Types (OLE DB and ODBC).

Comparability for IRowsetFind

All comparison operators are allowed for the new date/time types, because they appear as string types rather than date/time types.

See Also

Concepts