与 SQL Server 早期版本的增强日期和时间类型行为 (ODBC)Enhanced Date and Time Type Behavior with Previous SQL Server Versions (ODBC)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

本主题说明当使用增强的日期和时间功能的客户端应用程序与早于 SQL ServerSQL ServerSQL Server 2008SQL Server 2008 版本通信时的预期行为,以及使用 Microsoft 数据访问组件、Windows 数据访问组件或早于 SQL ServerSQL ServerSQL Server 2008SQL Server 2008 Native Client 版本的客户端应用程序向支持增强的日期和时间功能的服务器发送命令时的预期行为。This topic describes the expected behavior when a client application that uses enhanced date and time features communicates with a version of SQL ServerSQL Server earlier than SQL Server 2008SQL Server 2008, and when a client application using Microsoft Data Access Components, Windows Data Access Components, or a version of SQL ServerSQL Server Native Client earlier than SQL Server 2008SQL Server 2008 sends commands to a server that supports enhanced date and time features.

下级客户端行为Down-Level Client Behavior

使用早于 SQL ServerSQL ServerSQL Server 2008SQL Server 2008 Native Client 版本编译的客户端应用程序将新的日期/时间类型看作 nvarchar 列。Client applications that were compiled using a version of SQL ServerSQL Server Native Client prior to SQL Server 2008SQL Server 2008 see the new date/time types as nvarchar columns. 列内容是文本表示形式,如 "数据格式:字符串和文字" 一节中的 "数据 类型支持 ODBC 日期和时间改进" 一节中所述。The column contents are the literal representations, as described in "Data Formats: Strings and Literals" section of Data Type Support for ODBC Date and Time Improvements. 列大小是为列指定的秒小数精度的最大文字长度。The column size is the maximum literal length for the fractional seconds precision specified for the column.

目录 API 将返回与返回给客户端的下级数据类型代码(例如 nvarchar)和相关的下级表示法(例如相应的文字格式)一致的元数据。Catalog APIs will return metadata consistent with the down-level data type code returned to the client (for example, nvarchar) and the associated down-level representation (for example, the appropriate literal format). 不过,返回的数据类型名称将为 real SQL Server 2008SQL Server 2008 类型名称。However, the data type name returned will be the real SQL Server 2008SQL Server 2008 type name.

由 SQLDescribeCol、SQLDescribeParam、SQGetDescField 和 SQLColAttribute 返回的语句元数据将返回与所有方面的下级类型一致的元数据,包括类型名称。Statement metadata returned by SQLDescribeCol, SQLDescribeParam, SQGetDescField, and SQLColAttribute will return metadata that is consistent with the down-level type in all respects, including the type name. 此类下级类型的一个示例是 nvarcharAn example of such a down-level type is nvarchar.

当下级客户端应用程序针对 SQL Server 2008SQL Server 2008 (或更高版本运行时) 服务器上对日期/时间类型进行架构更改时,预期的行为如下所示:When a down-level client application runs against a SQL Server 2008SQL Server 2008 (or later) server on which schema changes to date/time types have been made, the expected behavior is as follows:

SQL Server 2005 类型SQL Server 2005 type SQL Server 2008SQL Server 2008(或更高版本)类型(or later) Type ODBC 客户端类型ODBC client type 结果转换(SQL 到 C)Result conversion (SQL to C) 参数转换(C 到 SQL)Parameter conversion (C to SQL)
datetimeDatetime DateDate SQL_C_TYPE_DATESQL_C_TYPE_DATE OKOK 确定 (1) OK (1)
SQL_C_TYPE_TIMESTAMPSQL_C_TYPE_TIMESTAMP 时间字段设置为零。Time fields set to zero. 成功 (2)OK (2)

如果时间字段非零,则失败。Fails if time field is non-zero. 适用于 SQL Server 2005 (9.x)SQL Server 2005 (9.x)Works with SQL Server 2005 (9.x)SQL Server 2005 (9.x).
Time(0)Time(0) SQL_C_TYPE_TIMESQL_C_TYPE_TIME OKOK 确定 (1) OK (1)
SQL_C_TYPE_TIMESTAMPSQL_C_TYPE_TIMESTAMP 日期字段设置为当前日期。Date fields set to current date. 成功 (2)OK (2)

忽略日期。Date ignored. 如果秒的小数部分非零,则将失败。Fails if fractional seconds are non-zero. 适用于 SQL Server 2005 (9.x)SQL Server 2005 (9.x)Works with SQL Server 2005 (9.x)SQL Server 2005 (9.x).
Time(7)Time(7) SQL_C_TIMESQL_C_TIME 失败-时间文本无效。Fails - invalid time literal. 确定 (1) OK (1)
SQL_C_TYPE_TIMESTAMPSQL_C_TYPE_TIMESTAMP 失败-时间文本无效。Fails - invalid time literal. 确定 (1) OK (1)
Datetime2 (3) Datetime2(3) SQL_C_TYPE_TIMESTAMPSQL_C_TYPE_TIMESTAMP OKOK 确定 (1) OK (1)
Datetime2 (7) Datetime2(7) SQL_C_TYPE_TIMESTAMPSQL_C_TYPE_TIMESTAMP OKOK 由客户端转换将值舍入到 1/300 秒。Value will be rounded to 1/300th second by client conversion.
SmalldatetimeSmalldatetime DateDate SQL_C_TYPE_DATESQL_C_TYPE_DATE OKOK OKOK
SQL_C_TYPE_TIMESTAMPSQL_C_TYPE_TIMESTAMP 时间字段设置为零。Time fields set to zero. 成功 (2)OK (2)

如果时间字段非零,则失败。Fails if time field is non-zero. 适用于 SQL Server 2005 (9.x)SQL Server 2005 (9.x)Works with SQL Server 2005 (9.x)SQL Server 2005 (9.x).
Time(0)Time(0) SQL_C_TYPE_TIMESQL_C_TYPE_TIME OKOK OKOK
SQL_C_TYPE_TIMESTAMPSQL_C_TYPE_TIMESTAMP 日期字段设置为当前日期。Date fields set to current date. 成功 (2)OK (2)

忽略日期。Date ignored. 如果秒的小数部分非零,则失败。Fails if fractional seconds non-zero.

适用于 SQL Server 2005 (9.x)SQL Server 2005 (9.x)Works with SQL Server 2005 (9.x)SQL Server 2005 (9.x).
Datetime2(0)Datetime2(0) SQL_C_TYPE_TIMESTAMPSQL_C_TYPE_TIMESTAMP OKOK OKOK

符号含义Key to Symbols

符号Symbol 含义Meaning
11 如果已适用于 SQL Server 2005 (9.x)SQL Server 2005 (9.x),应继续适用于 SQL ServerSQL Server 的更新版本。If it worked with SQL Server 2005 (9.x)SQL Server 2005 (9.x) it should continue to work with a more recent version of SQL ServerSQL Server.
22 已与 SQL Server 2005 (9.x)SQL Server 2005 (9.x) 一起使用的应用程序在与 SQL ServerSQL Server 的更新版本一起使用时,可能失败。An application that worked with SQL Server 2005 (9.x)SQL Server 2005 (9.x) could fail with a more recent version of SQL ServerSQL Server.

请注意只考虑了常见的架构更改。Note that only common schema changes have been considered. 以下是常见的更改:The following are common changes:

  • 使用新类型,这种情况下在逻辑上应用程序只需要一个日期或时间值。Using a new type where logically an application requires only a date or time value. 但是,由于缺乏单独的日期和时间类型,会强制应用程序使用 datetime 或 smalldatetime。However, the application was forced to use datetime or smalldatetime due to the lack of separate date and time types.

  • 使用新类型以获得其他秒小数精度或准确性。Using a new type to gain additional fractional seconds precision or accuracy.

  • 转换到 datetime2,因为这是首选的日期和时间数据类型。Switching to datetime2 because this is the preferred date and time datatype.

SQLColumns、SQLProcedureColumns 和 SQLSpecialColumns 返回的列元数据Column Metadata Returned by SQLColumns, SQLProcedureColumns, and SQLSpecialColumns

对于日期/时间类型将返回以下列值:The following column values are returned for date/time types:

列类型Column Type datedate timetime smalldatetimesmalldatetime datetimedatetime datetime2datetime2 datetimeoffsetdatetimeoffset
DATA_TYPEDATA_TYPE SQL_WVARCHARSQL_WVARCHAR SQL_WVARCHARSQL_WVARCHAR SQL_TYPE_TIMESTAMPSQL_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMPSQL_TYPE_TIMESTAMP SQL_WVARCHARSQL_WVARCHAR SQL_WVARCHARSQL_WVARCHAR
TYPE_NAMETYPE_NAME datedate timetime smalldatetimesmalldatetime datetimedatetime datetime2datetime2 datetimeoffsetdatetimeoffset
COLUMN_SIZECOLUMN_SIZE 1010 8,10,168,10..16 1616 2323 19、21..2719, 21..27 26、28..3426, 28..34
BUFFER_LENGTHBUFFER_LENGTH 2020 16,20. 3216, 20..32 1616 1616 38,42,5438, 42..54 52,56。52, 56..68
DECIMAL_DIGITSDECIMAL_DIGITS NullNULL NullNULL 00 33 NullNULL NullNULL
SQL_DATA_TYPESQL_DATA_TYPE SQL_WVARCHARSQL_WVARCHAR SQL_WVARCHARSQL_WVARCHAR SQL_DATETIMESQL_DATETIME SQL_DATETIMESQL_DATETIME SQL_WVARCHARSQL_WVARCHAR SQL_WVARCHARSQL_WVARCHAR
SQL_DATETIME_SUBSQL_DATETIME_SUB NullNULL NullNULL SQL_CODE_TIMESTAMPSQL_CODE_TIMESTAMP SQL_CODE_TIMESTAMPSQL_CODE_TIMESTAMP NullNULL NullNULL
CHAR_OCTET_LENGTHCHAR_OCTET_LENGTH NullNULL NullNULL NullNULL NullNULL NullNULL NullNULL
SS_DATA_TYPESS_DATA_TYPE 00 00 111111 111111 00 00

SQLSpecialColumns 不返回 SQL_DATA_TYPE、SQL_DATETIME_SUB、CHAR_OCTET_LENGTH 或 SS_DATA_TYPE。SQLSpecialColumns does not return SQL_DATA_TYPE, SQL_DATETIME_SUB, CHAR_OCTET_LENGTH, or SS_DATA_TYPE.

SQLGetTypeInfo 返回的数据类型元数据Data Type Metadata Returned by SQLGetTypeInfo

对于日期/时间类型将返回以下列值:The following column values are returned for date/time types:

列类型Column Type datedate timetime smalldatetimesmalldatetime datetimedatetime datetime2datetime2 datetimeoffsetdatetimeoffset
TYPE_NAMETYPE_NAME datedate timetime smalldatetimesmalldatetime datetimedatetime datetime2datetime2 datetimeoffsetdatetimeoffset
DATA_TYPEDATA_TYPE SQL_WVARCHARSQL_WVARCHAR SQL_WVARCHARSQL_WVARCHAR SQL_TYPE_TIMESTAMPSQL_TYPE_TIMESTAMP SQL_TYPE_TIMESTAMPSQL_TYPE_TIMESTAMP SQL_WVARCHARSQL_WVARCHAR SQL_WVARCHARSQL_WVARCHAR
COLUMN_SIZECOLUMN_SIZE 1010 1616 1616 2323 2727 3434
LITERAL_PREFIXLITERAL_PREFIX '' '' '' '' '' ''
LITERAL_SUFFIXLITERAL_SUFFIX '' '' '' '' '' ''
CREATE_PARAMSCREATE_PARAMS NullNULL NullNULL NullNULL NullNULL NullNULL NullNULL
NULLABLENULLABLE SQL_NULLABLESQL_NULLABLE SQL_NULLABLESQL_NULLABLE SQL_NULLABLESQL_NULLABLE SQL_NULLABLESQL_NULLABLE SQL_NULLABLESQL_NULLABLE SQL_NULLABLESQL_NULLABLE
CASE_SENSITIVECASE_SENSITIVE SQL_FALSESQL_FALSE SQL_FALSESQL_FALSE SQL_FALSESQL_FALSE SQL_FALSESQL_FALSE SQL_FALSESQL_FALSE SQL_FALSESQL_FALSE
可搜索SEARCHABLE SQL_PRED_SEARCHABLESQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLESQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLESQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLESQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLESQL_PRED_SEARCHABLE SQL_PRED_SEARCHABLESQL_PRED_SEARCHABLE
UNSIGNED_ATTRIBUTEUNSIGNED_ATTRIBUTE NullNULL NullNULL NullNULL NullNULL NullNULL NullNULL
FXED_PREC_SCALEFXED_PREC_SCALE SQL_FALSESQL_FALSE SQL_FALSESQL_FALSE SQL_FALSESQL_FALSE SQL_FALSESQL_FALSE SQL_FALSESQL_FALSE SQL_FALSESQL_FALSE
AUTO_UNIQUE_VALUEAUTO_UNIQUE_VALUE NullNULL NullNULL NullNULL NullNULL NullNULL NullNULL
LOCAL_TYPE_NAMELOCAL_TYPE_NAME datedate timetime smalldatetimesmalldatetime datetimedatetime datetime2datetime2 datetimeoffsetdatetimeoffset
MINIMUM_SCALEMINIMUM_SCALE NullNULL NullNULL 00 33 NullNULL NullNULL
MAXIMUM_SCALEMAXIMUM_SCALE NullNULL NullNULL 00 33 NullNULL NullNULL
SQL_DATA_TYPESQL_DATA_TYPE SQL_WVARCHARSQL_WVARCHAR SQL_WVARCHARSQL_WVARCHAR SQL_DATETIMESQL_DATETIME SQL_DATETIMESQL_DATETIME SQL_WVARCHARSQL_WVARCHAR SQL_WVARCHARSQL_WVARCHAR
SQL_DATETIME_SUBSQL_DATETIME_SUB NullNULL NullNULL SQL_CODE_TIMESTAMPSQL_CODE_TIMESTAMP SQL_CODE_TIMESTAMPSQL_CODE_TIMESTAMP NullNULL NullNULL
NUM_PREC_RADIXNUM_PREC_RADIX NullNULL NullNULL NullNULL NullNULL NullNULL NullNULL
INTERVAL_PRECISIONINTERVAL_PRECISION NullNULL NullNULL NullNULL NullNULL NullNULL NullNULL
USERTYPEUSERTYPE 00 00 1212 2222 00 00

下级服务器行为Down-Level Server Behavior

连接到版本早于 SQL Server 2008SQL Server 2008 的服务器实例时,只要试图使用新的服务器类型或相关的元数据代码和描述符字段,都将导致返回 SQL_ERROR。When connected to a server instance of an earlier version that SQL Server 2008SQL Server 2008, any attempt to use the new server types or associated metadata codes and descriptor fields will result in SQL_ERROR being returned. 将生成以下诊断记录:具有 SQLSTATE HY004 和消息“SQL 数据类型对于连接的服务器版本无效”,或具有 07006 和消息“受限制的数据类型属性冲突”。A diagnostic record will be generated with SQLSTATE HY004 and the message "Invalid SQL data type for server version on connection", or with 07006 and "Restricted data type attribute violation".

另请参阅See Also

ODBC)(的日期和时间改进 Date and Time Improvements (ODBC)