sql_variant Support for Date and Time Types

sql_variant Support for Date and Time Types

 

Applies To: SQL Server 2016 Preview

This topic describes how the sql_variant data type supports enhanced date and time functionality.

The column attribute SQL_CA_SS_VARIANT_TYPE is used to return the C type of a variant result column. SQL Server 2008 introduces an additional attribute, SQL_CA_SS_VARIANT_SQL_TYPE, which sets the SQL type of a variant result column in the implementation row descriptor (IRD). SQL_CA_SS_VARIANT_SQL_TYPE can also be used in the implementation parameter descriptor (IPD) to specify the SQL type of a SQL_SS_TIME2 or SQL_SS_TIMESTAMPOFFSET parameter that has SQL_C_BINARY C type bound with type SQL_SS_VARIANT.

The new types SQL_SS_TIME2 and SQL_SS_TIMESTAMPOFFSET can be set by SQLColAttribute. SQL_CA_SS_VARIANT_SQL_TYPE can be returned by SQLGetDescField.

For result columns, the driver will convert from the variant to date/time types. For more information, see Conversions from SQL to C. When binding to SQL_C_BINARY, the buffer length must be large enough to receive the struct that corresponds to the SQL type.

For the SQL_SS_TIME2 and SQL_SS_TIMESTAMPOFFSET parameters, the driver will convert C values to sql_variant values, as described in the table below. If a parameter is bound as SQL_C_BINARY and the server type is SQL_SS_VARIANT, it will be treated as a binary value unless the application has set SQL_CA_SS_VARIANT_SQL_TYPE to some other SQL type. In this case, SQL_CA_SS_VARIANT_SQL_TYPE takes precedence; that is, if SQL_CA_SS_VARIANT_SQL_TYPE is set, it overrides the default behavior of deducing the variant SQL type from the C type.

C typeServer typeComments
SQL_C_CHARvarcharSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_WCHARnvarcarSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_TINYINTsmallintSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_STINYINTsmallintSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_SHORTsmallintSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_SSHORTsmallintSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_USHORTintSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_LONGintSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_SLONGintSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_ULONGbigintSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_SBIGINTbigintSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_FLOATrealSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_DOUBLEfloatSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_BITbitSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_UTINYINTtinyintSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_BINARYvarbinarySQL_CA_SS_VARIANT_SQL_TYPE is not set.
SQL_C_BINARYtimeSQL_CA_SS_VARIANT_SQL_TYPE = SQL_SS_TIME2

Scale is set to SQL_DESC_PRECISION (the DecimalDigits parameter of SQLBindParameter).
SQL_C_BINARYdatetimeoffsetSQL_CA_SS_VARIANT_SQL_TYPE = SQL_SS_TIMESTAMPOFFSET

Scale is set to SQL_DESC_PRECISION (the DecimalDigits parameter of SQLBindParameter).
SQL_C_TYPE_DATEdateSQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_TYPE_TIMEtime(0)SQL_CA_SS_VARIANT_SQL_TYPE is ignored.
SQL_C_TYPE_TIMESTAMPdatetime2Scale is set to SQL_DESC_PRECISION (the DecimalDigits parameter of SQLBindParameter).
SQL_C_NUMERICdecimalPrecision is set to SQL_DESC_PRECISION (the ColumnSize parameter of SQLBindParameter).

Scale set to SQL_DESC_SCALE (the DecimalDigits parameter of SQLBindParameter).
SQL_C_SS_TIME2timeSQL_CA_SS_VARIANT_SQL_TYPE is ignored
SQL_C_SS_TIMESTAMPOFFSETdatetimeoffsetSQL_CA_SS_VARIANT_SQL_TYPE is ignored

Date and Time Improvements (ODBC)

Community Additions

ADD
Show:
© 2016 Microsoft