与 SQL Server 早期版本的增强日期和时间类型行为 (ODBC)Enhanced Date and Time Type Behavior with Previous SQL Server Versions (ODBC)
适用于：Applies to: 
SQL ServerSQL Server（所有支持的版本）SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL DatabaseAzure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed InstanceAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics并行数据仓库Parallel Data Warehouse并行数据仓库Parallel Data WarehouseSQL ServerSQL Server（所有支持的版本）SQL ServerSQL Server (all supported versions) Azure SQL 数据库Azure SQL DatabaseAzure SQL 数据库Azure SQL DatabaseAzure SQL 托管实例Azure SQL Managed InstanceAzure SQL 托管实例Azure SQL Managed InstanceAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics并行数据仓库Parallel Data Warehouse并行数据仓库Parallel Data Warehouse
本主题说明当使用增强的日期和时间功能的客户端应用程序与早于 SQL ServerSQL Server 的 SQL Server 2008SQL Server 2008 版本通信时的预期行为，以及使用 Microsoft 数据访问组件、Windows 数据访问组件或早于 SQL ServerSQL Server 的 SQL 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 Server 的 SQL 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. 此类下级类型的一个示例是 nvarchar。An 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".