New Date/Time Features with Previous SQL Server Versions (OLE DB)
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 compiled with 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 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:
OLE DB client type |
SQL Server 2005 type |
SQL Server 2008 (or later) type |
Result conversion (server to client) |
Parameter conversion (client to server) |
|---|---|---|---|---|
DBTYPE_DBDATE |
Datetime |
Date |
OK |
OK |
DBTYPE_DBTIMESTAMP |
Time fields set to zero. |
IRowsetChange will fail due to string truncation if the time field is non-zero. |
||
DBTYPE_DBTIME |
Time(0) |
OK |
OK |
|
DBTYPE_DBTIMESTAMP |
Date fields set to current date. |
IRowsetChange will fail due to string truncation if fractional seconds are non-zero. Date is ignored. |
||
DBTYPE_DBTIME |
Time(7) |
Fails – invalid time literal. |
OK |
|
DBTYPE_DBTIMESTAMP |
Fails – invalid time literal. |
OK |
||
DBTYPE_DBTIMESTAMP |
Datetime2(3) |
OK |
OK |
|
DBTYPE_DBTIMESTAMP |
Datetime2(7) |
OK |
OK |
|
DBTYPE_DBDATE |
Smalldatetime |
Date |
OK |
OK |
DBTYPE_DBTIMESTAMP |
Time fields set to zero. |
IRowsetChange will fail due to string truncation if time field is non-zero. |
||
DBTYPE_DBTIME |
Time(0) |
OK |
OK |
|
DBTYPE_DBTIMESTAMP |
Date fields set to current date. |
IRowsetChange will fail due to string truncation if fractional seconds are non-zero. Date is ignored. |
||
DBTYPE_DBTIMESTAMP |
Datetime2(0) |
OK |
OK |
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
This section describes metadata for parameters, result columns, and schema rowsets for clients that are compiled with a version of SQL Server Native Client earlier than SQL Server 2008.
ICommandWithParameters::GetParameterInfo
The DBPARAMINFO structure returns the following information through the prgParamInfo parameter:
Parameter type |
wType |
ulParamSize |
bPrecision |
bScale |
|---|---|---|---|---|
date |
DBTYPE_WSTR |
10 |
~0 |
~0 |
time |
DBTYPE_WSTR |
8, 10..16 |
~0 |
~0 |
smalldatetime |
DBTYPE_DBTIMESTAMP |
16 |
16 |
0 |
datetime |
DBTYPE_DBTIMESTAMP |
16 |
23 |
3 |
datetime2 |
DBTYPE_WSTR |
19,21..27 |
~0 |
~0 |
datetimeoffset |
DBTYPE_WSTR |
26,28..34 |
~0 |
~0 |
Notice that some of these value ranges are not continuous; for example, 9 is missing in 8,10..16. This is due to the addition of a decimal point when fractional precision is greater than zero.
IColumnsRowset::GetColumnsRowset
The following columns are returned:
Column type |
DBCOLUMN_TYPE |
DBCOLUMN_COLUMNSIZE |
DBCOLUMN_PRECISION |
DBCOLUMN_SCALE, DBCOLUMN_DATETIMEPRECISION |
|---|---|---|---|---|
date |
DBTYPE_WSTR |
10 |
NULL |
NULL |
time |
DBTYPE_WSTR |
8, 10..16 |
NULL |
NULL |
smalldatetime |
DBTYPE_DBTIMESTAMP |
16 |
16 |
0 |
datetime |
DBTYPE_DBTIMESTAMP |
16 |
23 |
3 |
datetime2 |
DBTYPE_WSTR |
19,21..27 |
NULL |
NULL |
datetimeoffset |
DBTYPE_WSTR |
26,28..34 |
NULL |
NULL |
ColumnsInfo::GetColumnInfo
The DBCOLUMNINFO structure returns the following information:
Parameter Type |
wType |
ulColumnSize |
bPrecision |
bScale |
|---|---|---|---|---|
date |
DBTYPE_WSTR |
10 |
~0 |
~0 |
time(1..7) |
DBTYPE_WSTR |
8, 10..16 |
~0 |
~0 |
smalldatetime |
DBTYPE_DBTIMESTAMP |
16 |
16 |
0 |
datetime |
DBTYPE_DBTIMESTAMP |
16 |
23 |
3 |
datetime2 |
DBTYPE_WSTR |
19,21..27 |
~0 |
~0 |
datetimeoffset |
DBTYPE_WSTR |
26,28..34 |
~0 |
~0 |
Schema Rowsets
This section discusses metadata for parameters, result columns, and schema rowsets for new data types. This information is useful is you have a client provider developed using tools earlier than SQL Server 2008 SQL Server Native Client.
COLUMNS Rowset
The following column values are returned for date/time types:
Column type |
DATA_TYPE |
CHARACTER_MAXIMUM_LENGTH |
CHARACTER_OCTET_LENGTH |
DATETIME_PRECISION |
|---|---|---|---|---|
date |
DBTYPE_WSTR |
10 |
20 |
NULL |
time |
DBTYPE_WSTR |
8, 10..16 |
16,20..32 |
NULL |
smalldatetime |
DBTYPE_DBTIMESTAMP |
NULL |
NULL |
0 |
datetime |
DBTYPE_DBTIMESTAMP |
NULL |
NULL |
3 |
datetime2 |
DBTYPE_WSTR |
19,21..27 |
38,42..54 |
NULL |
datetimeoffset |
DBTYPE_WSTR |
26,28..34 |
52, 56..68 |
NULL |
PROCEDURE_PARAMETERS Rowset
The following column values are returned for date/time types:
Column type |
DATA_TYPE |
CHARACTER_MAXIMUM_LENGTH |
CHARACTER_OCTET_LENGTH |
TYPE_NAME LOCAL_TYPE_NAME |
|---|---|---|---|---|
date |
DBTYPE_WSTR |
10 |
20 |
date |
time |
DBTYPE_WSTR |
8, 10..16 |
16,20..32 |
time |
smalldatetime |
DBTYPE_DBTIMESTAMP |
NULL |
NULL |
smalldatetime |
datetime |
DBTYPE_DBTIMESTAMP |
NULL |
NULL |
datetime |
datetime2 |
DBTYPE_WSTR |
19,21..27 |
38,42..54 |
datetime2 |
datetimeoffset |
DBTYPE_WSTR |
26,28..34 |
52, 56..68 |
datetimeoffset |
PROVIDER_TYPES Rowset
The following rows are returned for date/time types:
Type -> Column |
date |
time |
smalldatetime |
datetime |
datetime2 |
datetimeoffset |
|---|---|---|---|---|---|---|
TYPE_NAME |
date |
time |
smalldatetime |
datetime |
datetime2 |
datetimeoffset |
DATA_TYPE |
DBTYPE_WSTR |
DBTYPE_WSTR |
DBTYPE_DBTIMESTAMP |
DBTYPE_DBTIMESTAMP |
DBTYPE_WSTR |
DBTYPE_WSTR |
COLUMN_SIZE |
10 |
16 |
16 |
23 |
27 |
34 |
LITERAL_PREFIX |
‘ |
‘ |
‘ |
‘ |
‘ |
‘ |
LITERAL_SUFFIX |
‘ |
‘ |
‘ |
‘ |
‘ |
‘ |
CREATE_PARAMS |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
IS_NULLABLE |
VARIANT_TRUE |
VARIANT_TRUE |
VARIANT_TRUE |
VARIANT_TRUE |
VARIANT_TRUE |
VARIANT_TRUE |
CASE_SENSITIVE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
SEARCHABLE |
DB_SEARCHABLE |
DB_SEARCHABLE |
DB_SEARCHABLE |
DB_SEARCHABLE |
DB_SEARCHABLE |
DB_SEARCHABLE |
UNSIGNED_ATTRIBUTE |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
FIXED_PREC_SCALE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
AUTO_UNIQUE_VALUE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
LOCAL_TYPE_NAME |
date |
time |
smalldatetime |
datetime |
datetime2 |
datetimeoffset |
MINIMUM_SCALE |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
MAXIMUM_SCALE |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
GUID |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
TYPELIB |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
VERSION |
NULL |
NULL |
NULL |
NULL |
NULL |
NULL |
IS_LONG |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
BEST_MATCH |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_TRUE |
VARIANT_FALSE |
VARIANT_FALSE |
IS_FIXEDLENGTH |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
VARIANT_FALSE |
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.