通过

datetime2 (Transact-SQL)

  • 项目

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 中的 SQL 终结点 Microsoft Fabric 中的仓库 Microsoft Fabric SQL 数据库

定义结合了 24 小时制时间的日期。 可将 datetime2 视作现有 datetime 类型的扩展，其数据范围更大，默认的小数精度更高，并具有可选的用户定义的精度。

datetime2 说明

属性 值
语法 datetime2 [ (fractional seconds precision) ]
使用情况 DECLARE @MyDatetime2 datetime2(7);
CREATE TABLE Table1 (Column1 datetime2(7));
默认的字符串文字格式

（用于下级客户端）		 yyyy-MM-dd HH:mm:ss[.nnnnnnn]

有关详细信息，请参阅 本文后面的下层客户端 的向后兼容性。
日期范围 0001-01-01 到 9999-12-31

公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日
时间范围 00:00:00 到 23:59:59.9999999
时区偏移量范围 无
各元素的范围 yyyy是一个四位数的数字，范围从00019999一年到，表示一年。

MM 是一个两位数的数字，范围为 01 121，表示指定年份中的一个月。

dd 是一个两位数的数字，范围从月份 01 到 31 月份，表示指定月份的一天。

HH 是一个两位数的数字，范围为 00 1 23，表示小时。

mm 是一个两位数的数字，范围为 00 1 59，表示分钟。

ss 是一个两位数的数字，范围为 00 1 59，表示第二个数字。

n*是一个从零到七位数的数字09999999，表示小数秒。 在 Informatica 中，当 n 小于3时，小数秒将被截断。
字符长度 最低 19 个位置 （yyyy-MM-dd HH:mm:ss） 到 27 最大值 （yyyy-MM-dd HH:mm:ss.0000000）
精度、小数位数 0 到 7 位数字，准确度为 100 纳秒（100 ns）。 默认精度为 7 位数。

在 Microsoft Fabric 数据仓库中，此精度可以是 0 到 6 的整数，没有默认值。 必须在 Microsoft Fabric 数据仓库中指定精度。
存储大小 1 精度小于 3 的 6 个字节。
精度为 3 和 4 的 6 个字节。

所有其他精度都需要 8 个字节。 2
精确度 100 纳秒
默认值 1900-01-01 00:00:00
日历 公历
用户定义的秒的小数部分精度 是
时区偏移量感知和保留 否
夏时制感知 否

1 提供的值用于未压缩的行存储。 使用数据压缩或列存储可能会更改每个精度的存储大小。 此外，磁盘和内存中的存储大小可能有所不同。 例如，采用批处理模式时，datetime2 值始终需要 8 个字节的内存。

2 当 datetime2 值转换为 varbinary 值时，会将额外的字节添加到 varbinary 值以存储精度。

有关数据类型元数据，请参阅 sys.systypes 或 TYPEPROPERTY。 某些日期和时间数据类型的精度和小数位数是可变的。 若要获取列的精度和小数位数，请参阅 COLUMNPROPERTY、 COL_LENGTH 或 sys.columns。

datetime2 支持的字符串文字格式

以下各表列出了适用于 datetime2 的支持的 ISO 8601 和 ODBC 字符串文字格式。 有关 datetime2 日期和时间部分的字母、数字、未分离和时间格式的信息，请参阅日期和时间。

ISO 8601 说明
yyyy-MM-ddTHH:mm:ss[.nnnnnnn] 此格式不受会话区域设置和SET DATEFORMAT会话区域设置的影响SET LANGUAGE。 例如T，字符串文本2024-05-02T19:58:47.1234567中包含冒号（:）和句点（.）。
ODBC 说明
{ ts 'yyyy-MM-dd HH:mm:ss[.nnnnnnn]' } 特定于 ODBC API：

小数点右侧的数字表示秒小数部分，可指定 0 到 7 位（100 纳秒）。

对 ANSI 和 ISO 8601 的遵从性

datetime2 符合 date 和 time 的 ANSI 和 ISO 8601 标准。

下级客户端的向后兼容性

某些下级客户端不支持 time、date、datetime2 和 datetimeoffset 数据类型。 下表显示了 SQL Server 上级实例与下级客户端之间的类型映射。

SQL Server 数据类型 传递给下级客户端的默认字符串文字格式 下级 ODBC 下级 OLEDB 下级 JDBC 下级 SQLCLIENT
time HH：mm：ss[.nnnnnnn] SQL_WVARCHAR 或 SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STR Java.sql.String String 或 SqString
date yyyy-MM-dd SQL_WVARCHAR 或 SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STR Java.sql.String String 或 SqString
datetime2 yyyy-MM-dd HH：mm：ss[.nnnnnnn] SQL_WVARCHAR 或 SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STR Java.sql.String String 或 SqString
datetimeoffset yyyy-MM-dd HH：mm：ss[.nnnnnnnnn] [+|-]hh：mm SQL_WVARCHAR 或 SQL_VARCHAR DBTYPE_WSTR 或 DBTYPE_STR Java.sql.String String 或 SqString

转换日期和时间数据

转换为日期和时间数据类型时，数据库引擎将拒绝无法识别为日期或时间的所有值。 有关对日期和时间数据使用 CAST 和 CONVERT 函数的信息，请参阅 CAST 和 CONVERT

将其他日期和时间类型转换为 datetime2 数据类型

本部分介绍其他日期和时间数据类型转换为 datetime2 数据类型时会发生什么。

从 date 转换时，会复制年、月和日。 时间部分设置为 00:00:00.0000000。 下面的代码显示将 date 值转换为 datetime2 值的结果。

DECLARE @date AS DATE = '12-21-16';

DECLARE @datetime2 AS DATETIME2 = @date;

SELECT @datetime2 AS '@datetime2',
       @date AS '@date';

结果集如下。

@datetime2                  @date
--------------------------- ----------
2016-12-21 00:00:00.0000000 2016-12-21

当转换从 time（n）开始时，将复制时间组件，并将日期组件设置为 1900-01-01。 以下示例显示了将 time（7） 值转换为 datetime2 值的结果。

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

DECLARE @datetime2 AS DATETIME2 = @time;

SELECT @datetime2 AS '@datetime2',
       @time AS '@time';

结果集如下。

@datetime2                  @time
--------------------------- ----------------
1900-01-01 12:10:16.1234567 12:10:16.1234567

从 smalldatetime 转换时，会复制小时和分钟。 秒和秒的小数部分设置为 0。 下面的代码显示将 smalldatetime 值转换为 datetime2 值的结果。

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

DECLARE @datetime2 AS DATETIME2 = @smalldatetime;

SELECT @datetime2 AS '@datetime2',
       @smalldatetime AS '@smalldatetime';

结果集如下。

@datetime2                  @smalldatetime
--------------------------- -----------------------
2016-12-01 12:32:00.0000000 2016-12-01 12:32:00

从 datetimeoffset(n) 转换时，会复制日期和时间部分。 时区被截断。 下面的示例显示了将 datetimeoffset(7) 值转换为 datetime2 值的结果。

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

DECLARE @datetime2 AS DATETIME2 = @datetimeoffset;

SELECT @datetime2 AS '@datetime2',
       @datetimeoffset AS '@datetimeoffset';

结果集如下。

@datetime2                  @datetimeoffset
--------------------------- ----------------------------------
2016-10-23 12:45:37.1234567 2016-10-23 12:45:37.1234567 +10:00

从 datetime 转换时，会复制日期和时间。 小数精度扩展到 7 位。 下面的示例显示了将 datetime 值转换为 datetime2 值的结果。

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

DECLARE @datetime2 AS DATETIME2 = @datetime;

SELECT @datetime2 AS '@datetime2',
       @datetime AS '@datetime';

结果集如下。

@datetime2                  @datetime
----------------------- ---------------------------
2016-10-23 12:45:37.3333333 2016-10-23 12:45:37.333

使用 datetime 时显式强制转换为 datetime2

在数据库兼容性级别 130 及更高级别下，从日期时间到 datetime2 数据类型的隐式转换通过考虑小数毫秒（导致不同的转换值）来提高准确性，如前一示例所示。 每当存在日期/时间与 datetime2 数据类型之间的混合比较方案时，使用显式强制转换为 datetime2 数据类型。 有关详细信息，请参阅 SQL Server 和Azure SQL 数据库改进，以处理某些数据类型和不常见操作。

将字符串文本转换为 datetime2

如果字符串所有部分的格式均有效，则允许从字符串文字转换为日期和时间类型。 否则，将引发运行时错误。 不指定样式的隐式转换或显式转换（从日期和时间类型到字符串文本）采用当前会话的默认格式。 下表显示用于将字符串文字转换为 datetime2 数据类型的规则。

输入字符串文字 datetime2（n）
ODBC DATE ODBC 字符串文字映射到 datetime 数据类型。 从ODBC DATETIME文本到 datetime2 类型的任何赋值操作都会导致日期/时间与转换规则定义的此类型之间的隐式转换。
ODBC TIME 请参阅上一 ODBC DATE 规则。
ODBC DATETIME 请参阅上一 ODBC DATE 规则。
仅 DATE 部件 TIME 默认为 00:00:00.
仅 TIME 部件 DATE 默认为 1900-01-01.
仅 TIMEZONE 提供默认值。
DATE + TIME 琐碎。
DATE + TIMEZONE 不允许。
TIME + TIMEZONE 部件 DATE 默认为 1900-1-1。 TIMEZONE 将忽略输入。
DATE + TIME + TIMEZONE 使用本地 DATETIME 。

示例

下例比较了将一个字符串分别转换为各种 date 和 time 数据类型时所产生的结果 。

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';

结果集如下。

Data type 输出
time 12:35:29.1234567
date 2007-05-08
smalldatetime 2007-05-08 12:35:00
datetime 2007-05-08 12:35:29.123
datetime2 2007-05-08 12:35:29.1234567
datetimeoffset 2007-05-08 12:35:29.1234567 +12:15

相关内容