Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
This topic provides information about COLUMNS rowset and PROCEDURE_PARAMETERS rowset. This information relates to the OLE DB date and time enhancements introduced in SQL Server 2008.
COLUMNS Rowset
The following column values are returned for date/time types:
| Column Type | DATA_TYPE | COLUMN_FLAGS, DBCOLUMFLAGS_SS_ISVARIABLESCALE | DATETIME_PRECISION |
|---|---|---|---|
| date | DBTYPE_DBDATE | Clear | 0 |
| time | DBTYPE_DBTIME2 | Set | 0..7 |
| smalldatetime | DBTYPE_DBTIMESTAMP | Clear | 0 |
| datetime | DBTYPE_DBTIMESTAMP | Clear | 3 |
| datetime2 | DBTYPE_DBTIMESTAMP | Set | 0..7 |
| datetimeoffset | DBTYPE_DBTIMESTAMPOFFSET | Set | 0..7 |
In COLUMN_FLAGS, DBCOLUMNFLAGS_ISFIXEDLENGTH is always true for date/time types and the following flags are always false:
DBCOLUMNFLAGS_CACHEDEFERRED
DBCOLUMNFLAGS_ISBOOKMARK
DBCOLUMNFLAGS_ISCHAPTER
DBCOLUMNFLAGS_ISLONG
DBCOLUMNFLAGS_ISROWID
DBCOLUMNFLAGS_ISROWVER
DBCOLUMNFLAGS_MAYDEFER
The remaining flags (DBCOLUMNFLAGS_ISNULLABLE, DBCOLUMNFLAGS_MAYBENULL, DBCOLUMNFLAGS_WRITE, and DBCOLUMNFLAGS_WRITEUNKNOWN) might be set, depending on how the column is defined.
A new flag, DBCOLUMNFLAGS_SS_ISVARIABLESCALE, is provided in COLUMN_FLAGS to allow an application to determine the server type of columns where DATA_TYPE is DBTYPE_DBTIMESTAMP. DATETIME_PRECISION must also be used to identify the server type.
DBCOLUMNFLAGS_SS_ISVARIABLESCALE is only valid when connected to a SQL Server 2008 or later server. DBCOLUMNFLAGS_SS_ISFIXEDSCALE is undefined when connected to down-level servers.
PROCEDURE_PARAMETERS Rowset
DATA_TYPE contains the same values as the COLUMNS schema rowset and TYPE_NAME contains the server type.
A new column, SS_DATETIME_PRECISION, has been added to return the precision of the type as in the DATETIME_PRECISION column, similar to the COLUMNS rowset.
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_DBDATE | DBTYPE_DBTIME2 | DBTYPE_DBTIMESTAMP | DBTYPE_DBTIMESTAMP | DBTYPE_DBTIMESTAMP | DBTYPE_DBTIMESTAMPOFFSET |
| COLUMN_SIZE | 10 | 16 | 16 | 23 | 27 | 34 |
| LITERAL_PREFIX | ' | ' | ' | ' | ' | ' |
| LITERAL_SUFFIX | ' | ' | ' | ' | ' | ' |
| CREATE_PARAMS | NULL | scale | NULL | NULL | scale | scale |
| 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 | 0 | NULL | NULL | 0 | 0 |
| MAXIMUM_SCALE | NULL | 7 | NULL | NULL | 7 | 7 |
| 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_TRUE | VARIANT_TRUE | VARIANT_TRUE | VARIANT_TRUE unless one of the following is true: - Is client connected to a down-level server. - The data type compatibility connection property specifies a compatibility level that equals 80. |
VARIANT_TRUE unless one of the following is true: - Is client connected to a down-level server. - The data type compatibility connection property specifies a compatibility level that equals 80. |
VARIANT_TRUE |
| IS_FIXEDLENGTH | VARIANT_TRUE | VARIANT_TRUE | VARIANT_TRUE | VARIANT_TRUE | VARIANT_TRUE | VARIANT_TRUE |
OLE DB only defines MINIMUM_SCALE and MAXIMUM_SCALE for numeric and decimal types, so SQL Server Native Client's use of these columns for time, datetime2 and datetimeoffset is non-standard.