datetime2 (Transact-SQL)datetime2 (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

定义结合了 24 小时制时间的日期。Defines a date that is combined with a time of day that is based on 24-hour clock. 可将 datetime2 视作现有 datetime 类型的扩展,其数据范围更大,默认的小数精度更高,并具有可选的用户定义的精度 。datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

datetime2 说明datetime2 description

属性Property ReplTest1Value
语法Syntax datetime2 [ (fractional seconds precision) ] datetime2 [ (fractional seconds precision) ]
用法Usage DECLARE @MyDatetime2 datetime2(7) DECLARE @MyDatetime2 datetime2(7)

CREATE TABLE Table1 ( Column1 datetime2(7) )CREATE TABLE Table1 ( Column1 datetime2(7) )
默认的字符串文字格式Default string literal format

(用于下级客户端)(used for down-level client)
YYYY-MM-DD hh:mm:ss[.fractional seconds]YYYY-MM-DD hh:mm:ss[.fractional seconds]

有关详细信息,请参阅后面的“下级客户端的向后兼容性”部分。For more information, see the "Backward Compatibility for Down-level Clients" section that follows.
日期范围Date range 0001-01-01 到 31.12.990001-01-01 through 9999-12-31

公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日January 1,1 CE through December 31, 9999 CE
时间范围Time range 00:00:00 到 23:59:59.999999900:00:00 through 23:59:59.9999999
时区偏移量范围Time zone offset range NoneNone
各元素的范围Element ranges YYYY 是一个四位数,范围从 0001 到 9999,表示年份。YYYY is a four-digit number, ranging from 0001 through 9999, that represents a year.

MM 是一个两位数,范围从 01 到 12,它表示指定年份中的月份。MM is a two-digit number, ranging from 01 to 12, that represents a month in the specified year.

DD 是一个两位数,范围为 01 到 31(具体取决于月份),它表示指定月份中的某一天。DD is a two-digit number, ranging from 01 to 31 depending on the month, that represents a day of the specified month.

hh 是一个两位数,范围从 00 到 23,它表示小时。hh is a two-digit number, ranging from 00 to 23, that represents the hour.

mm 是一个两位数,范围从 00 到 59,它表示分钟。mm is a two-digit number, ranging from 00 to 59, that represents the minute.

ss 是一个两位数,范围从 00 到 59,它表示秒钟。ss is a two-digit number, ranging from 00 to 59, that represents the second.

n* 代表 0 到 7 位数字,范围从 0 到 9999999,它表示秒小数部分。n* is a zero- to seven-digit number from 0 to 9999999 that represents the fractional seconds. 在 Informatica 中,当 n > 3 时,秒的小数部分会被截断。In Informatica, the fractional seconds will be truncated when n > 3.
字符长度Character length 最低 19 位 (YYYY-MM-DD hh:mm:ss ),最高 27 位 (YYYY-MM-DD hh:mm:ss.0000000)19 positions minimum (YYYY-MM-DD hh:mm:ss ) to 27 maximum (YYYY-MM-DD hh:mm:ss.0000000)
精度、小数位数Precision, scale 0 至 7 位,准确度为 100ns。0 to 7 digits, with an accuracy of 100ns. 默认精度为 7 位数。The default precision is 7 digits.
存储大小Storage size 精度小于 3 的 6 个字节。6 bytes for precision less than 3.
精度为 3 和 4 的 6 个字节。7 bytes for precision 3 or 4.
所有其他精度则需要 8 个字节。1All other precision require 8 bytes.1
精确度Accuracy 100 纳秒100 nanoseconds
默认值Default value 1900-01-01 00:00:001900-01-01 00:00:00
日历Calendar 公历Gregorian
用户定义的秒的小数部分精度User-defined fractional second precision Yes
时区偏移量感知和保留Time zone offset aware and preservation No
夏时制感知Daylight saving aware No

1 datetime2 值的第一个字节将存储值精度,这意味着 datetime2 值所需的实际存储 是上表中指示的存储大小加上 1 个额外字节,用于存储精度。1 The first byte of a datetime2 value stores the precision of the value, which means the actual storage required for a datetime2 value is the storage size indicated in the table above plus 1 additional byte to store the precision. 这使 datetime2 值的最大大小为 9 个字节 - 1 个字节用于存储精度,另外 8 个字节用于存储最大数据精度。This makes the maximum size of a datetime2 value 9 bytes - 1 byte stores precision plus 8 bytes for data storage at maximum precision.

有关数据类型元数据,请参阅 sys.systypes (Transact-SQL)TYPEPROPERTY (Transact-SQL)For data type metadata, see sys.systypes (Transact-SQL) or TYPEPROPERTY (Transact-SQL). 某些日期和时间数据类型的精度和小数位数是可变的。Precision and scale are variable for some date and time data types. 若要获取列的精度和小数位数,请参阅 COLUMNPROPERTY (Transact-SQL)COL_LENGTH (Transact-SQL)sys.columns (Transact-SQL)To obtain the precision and scale for a column, see COLUMNPROPERTY (Transact-SQL), COL_LENGTH (Transact-SQL), or sys.columns (Transact-SQL).

datetime2 支持的字符串文字格式Supported string literal formats for datetime2

以下各表列出了适用于 datetime2 的支持的 ISO 8601 和 ODBC 字符串文字格式 。The following tables list the supported ISO 8601 and ODBC string literal formats for datetime2. 有关 datetime2 日期和时间部分的字母、数值、未分隔和时间格式的信息,请参阅 date (Transact-SQL)time (Transact-SQL)For information about alphabetical, numeric, unseparated, and time formats for the date and time parts of datetime2, see date (Transact-SQL) and time (Transact-SQL).

ISO 8601ISO 8601 “说明”Descriptions
YYYY-MM-DDThh:mm:ss[.nnnnnnn]YYYY-MM-DDThh:mm:ss[.nnnnnnn]

YYYY-MM-DDThh:mm:ss[.nnnnnnn]YYYY-MM-DDThh:mm:ss[.nnnnnnn]
此格式不受 SET LANGUAGE 和 SET DATEFORMAT 会话区域设置的影响。This format is not affected by the SET LANGUAGE and SET DATEFORMAT session locale settings. 包括在字符串内的 T、冒号 (:) 和句点 (.),例如“2007-05-02T19:58:47.1234567” 。The T, the colons (:) and the period (.) are included in the string literal, for example '2007-05-02T19:58:47.1234567'.
ODBCODBC 描述Description
{ ts 'yyyy-mm-dd hh:mm:ss[.fractional seconds]' }{ ts 'yyyy-mm-dd hh:mm:ss[.fractional seconds]' } 特定于 ODBC API:ODBC API specific:

小数点右侧的数字表示秒小数部分,可指定 0 到 7 位(100 纳秒)。The number of digits to the right of the decimal point, which represents the fractional seconds, can be specified from 0 up to 7 (100 nanoseconds).

对 ANSI 和 ISO 8601 的遵从性ANSI and ISO 8601 compliance

datetime2 符合 datetime 的 ANSI 和 ISO 8601 标准 。The ANSI and ISO 8601 compliance of date and time apply to datetime2.

下级客户端的向后兼容性Backward Compatibility for Down-level Clients

某些下级客户端不支持 time、time、datetime2 和 datetimeoffset 数据类型 。Some down-level clients do not support the time, date, datetime2 and datetimeoffset data types. 下表显示了 SQL ServerSQL Server 上级实例与下级客户端之间的类型映射。The following table shows the type mapping between an up-level instance of SQL ServerSQL Server and down-level clients.

SQL ServerSQL Server 数据类型data type 传递给下级客户端的默认字符串文字格式Default string literal format passed to down-level client 下级 ODBCDown-level ODBC 下级 OLEDBDown-level OLEDB 下级 JDBCDown-level JDBC 下级 SQLCLIENTDown-level SQLCLIENT
timetime hh:mm:ss[.nnnnnnn]hh:mm:ss[.nnnnnnn] SQL_WVARCHAR 或 SQL_VARCHARSQL_WVARCHAR or SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STRDBTYPE_WSTRor DBTYPE_STR Java.sql.StringJava.sql.String String 或 SqStringString or SqString
datedate YYYY-MM-DDYYYY-MM-DD SQL_WVARCHAR 或 SQL_VARCHARSQL_WVARCHAR or SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STRDBTYPE_WSTRor DBTYPE_STR Java.sql.StringJava.sql.String String 或 SqStringString or SqString
datetime2datetime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn]YYYY-MM-DD hh:mm:ss[.nnnnnnn] SQL_WVARCHAR 或 SQL_VARCHARSQL_WVARCHAR or SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STRDBTYPE_WSTRor DBTYPE_STR Java.sql.StringJava.sql.String String 或 SqStringString or SqString
datetimeoffsetdatetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mmYYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm SQL_WVARCHAR 或 SQL_VARCHARSQL_WVARCHAR or SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STRDBTYPE_WSTRor DBTYPE_STR Java.sql.StringJava.sql.String String 或 SqStringString or SqString

转换日期和时间数据Converting date and time data

当转换为日期和时间数据类型时,SQL ServerSQL Server 将会拒绝它无法识别为日期或时间的所有值。When you convert to date and time data types, SQL ServerSQL Server rejects all values it cannot recognize as dates or times. 有关日期和时间数据使用 CAST 和 CONVERT 函数的信息,请参阅 CAST 和 CONVERT (Transact-SQL)For information about using the CAST and CONVERT functions with date and time data, see CAST and CONVERT (Transact-SQL)

将其他日期和时间类型转换为 datetime2 数据类型Converting other date and time types to the datetime2 data type

本部分介绍其他日期和时间数据类型转换为 datetime2 数据类型时会发生什么 。This section describes what occurs when other date and time data types are converted to the datetime2 data type.

从 date 转换时,会复制年、月和日 。When the conversion is from date, the year, month and day are copied. 时间部分设置为 00:00:00.0000000。The time component is set to 00:00:00.0000000. 下面的代码显示将 date 值转换为 datetime2 值的结果。The following code shows the results of converting a date value to a datetime2 value.

DECLARE @date date = '12-21-16';
DECLARE @datetime2 datetime2 = @date;

SELECT @datetime2 AS '@datetime2', @date AS '@date';
  
--Result  
--@datetime2                  @date
----------------------------- ----------
--2016-12-21 00:00:00.0000000 2016-12-21

从 time(n) 转换时,会复制时间部分,日期部分设置为“1900-01-01” 。When the conversion is from time(n), the time component is copied, and the date component is set to '1900-01-01'. 下面的示例显示了将 time(7) 值转换为 datetime2 值的结果。The following example shows the results of converting a time(7) value to a datetime2 value.

DECLARE @time time(7) = '12:10:16.1234567';
DECLARE @datetime2 datetime2 = @time;

SELECT @datetime2 AS '@datetime2', @time AS '@time';
  
--Result  
--@datetime2                  @time
----------------------------- ----------------
--1900-01-01 12:10:16.1234567 12:10:16.1234567

从 smalldatetime 转换时,会复制小时和分钟。 When the conversion is from smalldatetime, the hours and minutes are copied. 秒和秒的小数部分设置为 0。The seconds and fractional seconds are set to 0. 下面的代码显示将 smalldatetime 值转换为 datetime2 值的结果。The following code shows the results of converting a smalldatetime value to a datetime2 value.

DECLARE @smalldatetime smalldatetime = '12-01-16 12:32';
DECLARE @datetime2 datetime2 = @smalldatetime;

SELECT @datetime2 AS '@datetime2', @smalldatetime AS '@smalldatetime'; 
  
--Result  
--@datetime2                  @smalldatetime
----------------------------- -----------------------
--2016-12-01 12:32:00.0000000 2016-12-01 12:32:00 

从 datetimeoffset(n) 转换时,会复制日期和时间部分 。When the conversion is from datetimeoffset(n), the date and time components are copied. 时区被截断。The time zone is truncated. 下面的示例显示了将 datetimeoffset(7) 值转换为 datetime2 值的结果。The following example shows the results of converting a datetimeoffset(7) value to a datetime2 value.

DECLARE @datetimeoffset datetimeoffset(7) = '2016-10-23 12:45:37.1234567 +10:0';
DECLARE @datetime2 datetime2 = @datetimeoffset;

SELECT @datetime2 AS '@datetime2', @datetimeoffset AS '@datetimeoffset'; 
  
--Result  
--@datetime2                  @datetimeoffset
----------------------------- ----------------------------------
--2016-10-23 12:45:37.1234567 2016-10-23 12:45:37.1234567 +10:00

从 datetime 转换时,会复制日期和时间 。When the conversion is from datetime, the date and time are copied. 小数精度扩展到 7 位。The fractional precision is extended to 7 digits. 下面的示例显示了将 datetime 值转换为 datetime2 值的结果。The following example shows the results of converting a datetime value to a datetime2 value.

DECLARE @datetime datetime = '2016-10-23 12:45:37.333';
DECLARE @datetime2 datetime2 = @datetime;

SELECT @datetime2 AS '@datetime2', @datetime AS '@datetime';
   
--Result  
--@datetime2                  @datetime
------------------------- ---------------------------
--2016-10-23 12:45:37.3333333 2016-10-23 12:45:37.333

备注

在数据库兼容性级别 130 下,通过考虑导致不同转换值的毫秒小数部分,从 datetime 到 datetime2 数据类型的隐式转换更加准确,如上例中所示。Under database compatibility level 130, implicit conversions from datetime to datetime2 data types show improved accuracy by accounting for the fractional milliseconds, resulting in different converted values, as seen in the example above. 只要 datetime 和 datetime2 数据类型之间存在混合比较情况,就需要使用 datetime2 数据类型的隐式转换。Use explicit casting to datetime2 datatype whenever a mixed comparison scenario between datetime and datetime2 datatypes exists. 有关详细信息,请参阅此 Microsoft 支持文章For more information, refer to this Microsoft Support Article.

将字符串文字转换为 datetime2Converting String Literals to datetime2

如果字符串所有部分的格式均有效,则允许从字符串文字转换为日期和时间类型。Conversions from string literals to date and time types are permitted if all parts of the strings are in valid formats. 否则,将引发运行时错误。Otherwise, a runtime error is raised. 从日期和时间类型向字符串文字进行的未指定样式的隐式转换或显式转换将采用当前会话的默认格式。Implicit conversions or explicit conversions that do not specify a style, from date and time types to string literals will be in the default format of the current session. 下表显示用于将字符串文字转换为 datetime2 数据类型的规则 。The following table shows the rules for converting a string literal to the datetime2 data type.

输入字符串文字Input string literal datetime2(n)datetime2(n)
ODBC DATEODBC DATE ODBC 字符串文字映射到 datetime 数据类型 。ODBC string literals are mapped to the datetime data type. 从 ODBC DATETIME 文字到 datetime2 类型的任何赋值操作都会导致在 datetime 与此类型之间按照转换规则的定义进行隐式转换 。Any assignment operation from ODBC DATETIME literals into datetime2 types will cause an implicit conversion between datetime and this type as defined by the conversion rules.
ODBC TIMEODBC TIME 请参阅前面的 ODBC DATE 规则。See previous ODBC DATE rule.
ODBC DATETIMEODBC DATETIME 请参阅前面的 ODBC DATE 规则。See previous ODBC DATE rule.
仅 DATEDATE only TIME 部分默认为 00:00:00。The TIME part defaults to 00:00:00.
仅 TIMETIME only DATE 部分默认为 1900-1-1。The DATE part defaults to 1900-1-1.
仅 TIMEZONETIMEZONE only 提供默认值。Default values are supplied.
DATE + TIMEDATE + TIME 无庸赘述Trivial
DATE + TIMEZONEDATE + TIMEZONE 不允许。Not allowed.
TIME + TIMEZONETIME + TIMEZONE DATE 部分默认为 1900-1-1。The DATE part defaults to 1900-1-1. 忽略 TIMEZONE 输入。TIMEZONE input is ignored.
DATE + TIME + TIMEZONEDATE + TIME + TIMEZONE 将使用本地 DATETIME。The local DATETIME will be used.

示例Examples

下例比较了将一个字符串分别转换为各种 date 和 time 数据类型时所产生的结果 。The following example compares the results of casting a string to each date and time data type.

SELECT   
     CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time'   
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date'   
    ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS   
        'smalldatetime'   
    ,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime'   
    ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS   
        'datetime2'  
    ,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS   
        'datetimeoffset';  

下面是结果集:Here is the result set.

数据类型Data type “输出”Output
timetime 12:35:29.12:35:29. 12345671234567
datedate 2007-05-082007-05-08
smalldatetimesmalldatetime 2007-05-08 12:35:002007-05-08 12:35:00
datetimedatetime 2007-05-08 12:35:29.1232007-05-08 12:35:29.123
datetime2datetime2 2007-05-08 12:35:29.2007-05-08 12:35:29. 12345671234567
datetimeoffsetdatetimeoffset 2007-05-08 12:35:29.1234567 +12:152007-05-08 12:35:29.1234567 +12:15

另请参阅See also

CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)