Edit

Share via

Metadata - Date and Time and Schema Rowsets in SQL Server Native Client

  • Article

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

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 (10.0.x).

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 (10.0.x) 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.

See Also

Metadata (OLE DB)