sql_variant Support for Date and Time Types
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)
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 (10.0.x) 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 type | Server type | Comments |
---|---|---|
SQL_C_CHAR | varchar | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_WCHAR | nvarcar | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_TINYINT | smallint | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_STINYINT | smallint | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_SHORT | smallint | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_SSHORT | smallint | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_USHORT | int | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_LONG | int | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_SLONG | int | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_ULONG | bigint | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_SBIGINT | bigint | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_FLOAT | real | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_DOUBLE | float | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_BIT | bit | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_UTINYINT | tinyint | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_BINARY | varbinary | SQL_CA_SS_VARIANT_SQL_TYPE is not set. |
SQL_C_BINARY | time | SQL_CA_SS_VARIANT_SQL_TYPE = SQL_SS_TIME2 Scale is set to SQL_DESC_PRECISION (the DecimalDigits parameter of SQLBindParameter). |
SQL_C_BINARY | datetimeoffset | SQL_CA_SS_VARIANT_SQL_TYPE = SQL_SS_TIMESTAMPOFFSET Scale is set to SQL_DESC_PRECISION (the DecimalDigits parameter of SQLBindParameter). |
SQL_C_TYPE_DATE | date | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_TYPE_TIME | time(0) | SQL_CA_SS_VARIANT_SQL_TYPE is ignored. |
SQL_C_TYPE_TIMESTAMP | datetime2 | Scale is set to SQL_DESC_PRECISION (the DecimalDigits parameter of SQLBindParameter). |
SQL_C_NUMERIC | decimal | Precision 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_TIME2 | time | SQL_CA_SS_VARIANT_SQL_TYPE is ignored |
SQL_C_SS_TIMESTAMPOFFSET | datetimeoffset | SQL_CA_SS_VARIANT_SQL_TYPE is ignored |
See Also
משוב
https://aka.ms/ContentUserFeedback.
בקרוב: במהלך 2024, נפתור בעיות GitHub כמנגנון המשוב לתוכן ונחליף אותו במערכת משוב חדשה. לקבלת מידע נוסף, ראה:שלח והצג משוב עבור