Muokkaa

Jaa


Metadata - Parameter and Result

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

This topic describes what is returned in the implementation parameter descriptor (IPD) and implementation row descriptor (IRD) fields for date and time data types.

Information Returned in IPD Fields

The following information is returned in the IPD fields:

Parameter type date time smalldatetime datetime datetime2 datetimeoffset
SQL_DESC_CASE_SENSITIVE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE
SQL_DESC_CONCISE_TYPE SQL_TYPE_DATE SQL_SS_TIME2 SQL_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMP SQL_SS_TIMESTAMPOFFSET
SQL_DESC_DATETIME_INTERVAL_CODE SQL_CODE_DATE 0 SQL_CODE_TIMESTAMP SQL_CODE_TIMESTAMP SQL_CODE_TIMESTAMP 0
SQL_DESC_DATETIME_INTERVAL_PRECISION 10 8,10..16 16 23 19, 21..27 26, 28..34
SQL_DESC_FIXED_PREC_SCALE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE
SQL_DESC_LENGTH 10 8,10..16 16 23 19, 21..27 26, 28..34
SQL_DESC_OCTET_LENGTH 6 12 4 8 16 20
SQL_DESC_PRECISION 0 0..7 0 3 0..7 0..7
SQL_DESC_SCALE 0 0..7 0 3 0..7 0..7
SQL_DESC_TYPE SQL_TYPE_DATE SQL_SS_TYPE_TIME2 SQL_DATETIME SQL_DATETIME SQL_DATETIME SQL_SS_TIMESTAMPOFFSET
SQL_DESC_TYPE_NAME date time smalldatetime in IRD, datetime2 in IPD datetime in IRD, datetime2 in IPD datetime2 datetimeoffset
SQL_CA_SS_VARIANT_TYPE SQL_C_TYPE_DATE SQL_C_TYPE_BINARY SQL_C_TYPE_TIMESTAMP SQL_C_TYPE_TIMESTAMP SQL_C_TYPE_TIMESTAMP SQL_C_TYPE_BINARY
SQL_CA_SS_VARIANT_SQL_TYPE SQL_TYPE_DATE SQL_SS_TIME2 SQL_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMP SQL_SS_TIMESTAMPOFFSET
SQL_CA_SS_SERVER_TYPE N/A N/A SQL_SS_TYPE_SMALLDATETIME SQL_SS_TYPE_DATETIME SQL_SS_TYPE_DEFAULT N/A

Sometimes there are discontinuities in value ranges. 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.

datetime2 is returned as the typename for smalldatetime and datetime because the driver uses this as a common type for transmitting all SQL_TYPE_TIMESTAMP values to the server.

SQL_CA_SS_VARIANT_SQL_TYPE is a new descriptor field. This field was added to the IRD and IPD to enable applications to specify the value type associated with sqlvariant (SQL_SSVARIANT) columns and parameters

SQL_CA_SS_SERVER_TYPE is a new IPD-only field to enable applications to control how values for parameters bound as SQL_TYPE_TYPETIMESTAMP (or as SQL_SS_VARIANT with a C type of SQL_C_TYPE_TIMESTAMP) are sent to the server. If SQL_DESC_CONCISE_TYPE is SQL_TYPE_TIMESTAMP (or is SQL_SS_VARIANT and the C type is SQL_C_TYPE_TIMESTAMP) when SQLExecute or SQLExecDirect is called, the value of SQL_CA_SS_SERVER_TYPE determines the tabular data stream (TDS) type of the parameter value, as follows:

Value of SQL_CA_SS_SERVER_TYPE Valid values for SQL_DESC_PRECISION Valid values for SQL_DESC_LENGTH TDS type
SQL_SS_TYPE_DEFAULT 0..7 19, 21..27 datetime2
SQL_SS_TYPE_SMALLDATETIME 0 19 smalldatetime
SQL_SS_TYPE_DATETIME 3 23 datetime

The default setting of SQL_CA_SS_SERVER_TYPE is SQL_SS_TYPE_DEFAULT. The settings of SQL_DESC_PRECISION and SQL_DESC_LENGTH are validated with the setting of SQL_CA_SS_SERVER_TYPE as described in the table above. If this validation fails, SQL_ERROR is returned and a diagnostic record is logged with SQLState 07006 and the message "Restricted data type attribute violation". This error is also returned if SQL_CA_SS_SERVER_TYPE is set to a value other than SQL_SS_TYPE DEFAULT and DESC_CONCISE_TYPE is not SQL_TYPE_TIMESTAMP. These validations are performed when descriptor consistency validation occurs, for example:

  • When SQL_DESC_DATA_PTR is changed.

  • At prepare or execute time (when SQLExecute, SQLExecDirect, SQLSetPos, or SQLBulkOperations is called).

  • When an application forces a non-deferred prepare by calling SQLPrepare with deferred prepare disabled, or by calling SQLNumResultCols, SQLDescribeCol, or SQLDescribeParam for a statement that is prepared but not executed.

When SQL_CA_SS_SERVER_TYPE is set by a call to SQLSetDescField, its value must be SQL_SS_TYPE_DEFAULT, SQL_SS_TYPE_SMALLDATETIME, or SQL_SS_TYPE_DATETIME. If this is not the case, SQL_ERROR is returned and a diagnostic record is logged with SQLState HY092 and the message "Invalid attribute/option identifier".

The SQL_CA_SS_SERVER_TYPE attribute can be used by applications that depend on functionality supported by datetime and smalldatetime, but not datetime2. For example, datetime2 requires the use of the dateadd and datediif functions, whereas datetime and smalldatetime also allow arithmetic operators. Most applications will not need to use this attribute and its use should be avoided.

Information Returned in IRD Fields

The following information is returned in the IRD fields:

Column Type date time smalldatetime datetime datetime2 datetimeoffset
SQL_DESC_AUTO_UNIQUE_VALUE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE
SQL_DESC_CASE_SENSITIVE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE
SQL_DESC_CONCISE_TYPE SQL_TYPE_DATE SQL_SS_TIME2 SQL_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMP SQL_SS_TIMESTAMPOFFSET
SQL_DESC_DATETIME_INTERVAL_CODE SQL_CODE_DATE 0 SQL_CODE_TIMESTAMP SQL_CODE_TIMESTAMP SQL_CODE_TIMESTAMP 0
SQL_DESC_DATETIME_INTERVAL_PRECISION 10 8,10..16 16 23 19, 21..27 26, 28..34
SQL_DESC_DISPLAY_SIZE 10 8,10..16 16 23 19, 21..27 26, 28..34
SQL_DESC_FIXED_PREC_SCALE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE SQL_FALSE
SQL_DESC_LENGTH 10 8,10..16 16 2 19, 21..27 26, 28..34
SQL_DESC_LITERAL_PREFIX ' ' ' ' ' '
SQL_DESC_LITERAL_SUFFIX ' ' ' ' ' '
SQL_DESC_LOCAL_TYPE_NAME date time smalldatetime datetime datetime2 datetimeoffset
SQL_DESC_OCTET_LENGTH 6 12 4 8 16 20
SQL_DESC_PRECISION 0 0..7 0 3 0..7 0..7
SQL_DESC_SCALE 0 0..7 0 3 0..7 0..7
SQL_DESC_SEARCHABLE SQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLE
SQL_DESC_TYPE SQL_DATETIME SQL_SS_TIME2 SQL_DATETIME SQL_DATETIME SQL_DATETIME SQL_SS_TIMESTAMPOFFSET
SQL_DESC_TYPE_NAME date time smalldatetime datetime datetime2 datetimeoffset
SQL_DESC_UNSIGNED SQL_TRUE SQL_TRUE SQL_TRUE SQL_TRUE SQL_TRUE SQL_TRUE

See Also

Metadata (ODBC)