Export (0) Print
Expand All

Parameter and Result Metadata

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

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.

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

Community Additions

ADD
Show:
© 2015 Microsoft