datetimeoffset (Transact-SQL)

适用于: SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics 分析平台系统 (PDW) Microsoft Fabric 中的 SQL 分析端点 Microsoft Fabric 中的仓库

定义一个日期,该日期与基于 24 小时制(如 datetime2)的一天时间相结合,并基于协调世界时(UTC)添加时区感知。

datetimeoffset 说明

属性
语法 DATETIMEOFFSET [ ( 秒小数精度 ) ]
使用情况 DECLARE @MyDatetimeoffset DATETIMEOFFSET(7);
CREATE TABLE Table1 (Column1 DATETIMEOFFSET(7));
默认字符串文本格式(用于下层客户端) yyyy-MM-dd HH:mm:ss[.nnnnnnnnn] [{+|-}hh:mm]

有关详细信息,请参阅 后面的下层客户端 的向后兼容性部分。
日期范围 0001-01-019999-12-31

公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日
时间范围 00:00:0023:59:59.9999999
时区偏移范围 -14:00+14:00
元素范围 yyyy是四位数字,范围从00019999表示一年。

MM 是两个数字,范围从一 0112,表示指定年份中的一个月。
dd 是两个数字,范围从月份 0131 根据月份,表示指定月份的一天。
HH 是表示小时数的两位数字,范围介于 00 1 到 232 位。
mm 是表示分钟数的两位数字,范围从 00 1 到 591。
ss 是两个数字,范围从 00 1 到 59,表示第二个数字。
n 为零到七位数字,范围从零 099999997 位,表示小数秒。
hh 是两个介于 1 -14 到 .0 +14之间的数字。
mm 是两个介于 1 00 到 .0 59之间的数字。
字符长度 最低 26 个位置 (yyyy-MM-dd HH:mm:ss {+|-}hh:mm) 到 34 最大值 (yyyy-MM-dd HH:mm:ss.nnnnnnn {+|-}hh:mm)
精度、小数位数 请参见下表。
存储大小 10 个字节,固定为默认值,默认值为 100-ns 小数秒精度。
准确性 100 纳秒
默认值 1900-01-01 00:00:00 00:00
日历 公历
用户定义的小数秒精度
时区偏移感知和保留
夏令时感知
指定的小数位数 结果 (精度, 小数位数) 列长度(以字节为单位) 秒的小数部分精度
datetimeoffset (34, 7) 10 7
datetimeoffset(0) (26, 0) 8 0 到 2
datetimeoffset(1) (28, 1) 8 0 到 2
datetimeoffset(2) (29, 2) 8 0 到 2
datetimeoffset(3) (30, 3) 9 3 到 4
datetimeoffset(4) (31, 4) 9 3 到 4
datetimeoffset(5) (32, 5) 10 5 到 7
datetimeoffset(6) (33, 6) 10 5 到 7
datetimeoffset(7) (34, 7) 10 5 到 7

datetimeoffset 支持的字符串文字格式

下表列出了 datetimeoffset 支持的 ISO 8601 字符串文字格式。 有关 datetimeoffset 日期和时间部分的字母、数字、未分离和时间格式的信息,请参阅 date (Transact-SQL)时间 (Transact-SQL)。

ISO 8601 说明
yyyy-MM-ddTHH:mm:ss[.nnnnnnnnn][{+|-}hh:mm] 这两种格式不受会话区域设置和SET DATEFORMAT会话区域设置的影响SET LANGUAGE。 日期时间offset 和 datetime 部件之间不允许空格。
yyyy-MM-ddTHH:mm:ss[.nnnnnnnnn]Z (UTC) 这种遵从 ISO 定义的格式表明 datetime 部分应采用协调世界时 (UTC) 表示。 例如, 1999-12-12 12:30:30.12345 -07:00 应表示为 1999-12-12 19:30:30.12345Z.

下例比较了将一个字符串分别转换为各种 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',
    CAST('2007-05-08 12:35:29.1234567+12:15' AS DATETIMEOFFSET(7)) AS 'datetimeoffset IS08601';

下面是结果集。

数据类型 输出
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
datetimeoffset IS08601 2007-05-08 12:35:29.1234567 +12:15

时区偏移量

时区偏移量指定某个 time 或 datetime 值相对于 UTC 的时区偏移量。 时区偏移量可以表示为 [+|-] hh:mm:

  • hh是两位数,范围介于0014时区偏移量和表示小时数。

  • mm 是两个数字,范围为 00 592,表示时区偏移量中的其他分钟数。

  • + (加)或 - (减)是时区偏移量的必需符号。 此符号指示是否从 UTC 时间添加或减去时区偏移量以获取本地时间。 时区偏移量的有效范围为 -14:00+14:00

时区偏移范围遵循 XSD 架构定义的 W3C XML 标准,与 SQL 2003 标准定义略有不同。 12:59 +14:00

可选的类型参数 fractional seconds precision 指定了秒小数部分的位数。 该值可以是一个 0 到 7(100 纳秒)的整数。 默认 的小数秒精度 为 100 ns(秒的小数部分的 7 位数字)。

此数据存储在数据库中,并以与 UTC 相同的方式在服务器中进行处理、比较、排序和索引。 时区偏移量保留在数据库中以供检索。

假定给定时区偏移量为夏令时(DST)感知,并针对 DST 期间内的任何给定 日期/ 时间进行调整。

对于 datetimeoffset 类型,在插入、更新、算术、转换或分配操作期间验证 UTC 和本地时间值(永久性或转换时区偏移量)。 检测任何无效的 UTC 或本地时间(永久性或转换后的时区偏移量) 日期/时间 值将引发无效值错误。 例如, 9999-12-31 10:10:00 在 UTC 中有效,但本地时间溢出到时区偏移量 +13:50

时区转换语法

SQL Server 2016 (13.x) 引入了 AT TIME ZONE 语法,以促进夏令时感知的通用时区转换。 在转换没有时区偏移量的数据时,此语法特别有用,因为数据具有时区偏移量。 若要转换为目标时区中的相应 datetimeoffset 值,请参阅 AT TIME ZONE

对 ANSI 和 ISO 8601 的遵从性

日期和时间文章的 ANSI 和 ISO 8601 合规性部分适用于 datetimeoffset。

下级客户端的向后兼容性

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

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

Microsoft Fabric 支持

在 Microsoft Fabric 中,当前无法使用 datetimeoffset 数据类型创建列,但可以使用 datetimeoffset 通过 AT TIME ZONE (Transact-SQL) 函数转换数据,例如:

SELECT
CAST(CAST('2024-07-03 00:00:00' AS DATETIMEOFFSET) AT TIME ZONE 'Pacific Standard Time' AS datetime2) AS PST

转换日期和时间数据

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

转换为 datetimeoffset 数据类型

本部分提供了一个示例,演示如何在不偏移到新的 datetimeoffset 数据类型列的情况下从数据类型更新数据。

首先,从 sys.time_zone_info 系统目录视图中验证时区名称。

SELECT * FROM sys.time_zone_info WHERE name = 'Pacific Standard Time';

以下示例使用 AT TIME ZONE 语法两次。 示例代码创建一个表 dbo.Audit,添加跨越多个夏令时更改的数据,并添加新 的 datetimeoffset 列。 我们假设该 AuditCreated 列是一个不带偏移量的 *datetime2 数据类型,并且是使用 UTC 时区编写的。

在语句中UPDATEAT TIME ZONE,语法首先将 UTC 时区偏移量添加到现有AuditCreated列数据,然后将数据从 UTC Pacific Standard Time转换为,从而正确调整过去夏令时范围在美国中的每个历史数据。

CREATE TABLE dbo.Audit (AuditCreated DATETIME2(0) NOT NULL);
GO

INSERT INTO dbo.Audit (AuditCreated)
VALUES ('1/1/2024 12:00:00');

INSERT INTO dbo.Audit (AuditCreated)
VALUES ('5/1/2024 12:00:00');

INSERT INTO dbo.Audit (AuditCreated)
VALUES ('12/1/2024 12:00:00');
GO

ALTER TABLE dbo.Audit
ADD AuditCreatedOffset DATETIMEOFFSET(0) NULL;
GO

DECLARE @TimeZone VARCHAR(50);

SELECT @TimeZone = [name]
FROM sys.time_zone_info
WHERE [name] = 'Pacific Standard Time';

UPDATE dbo.Audit
SET AuditCreatedOffset = AuditCreated
    AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone
WHERE AuditCreatedOffset IS NULL;
GO

SELECT *
FROM dbo.Audit;

下面是结果集:

AuditCreated         AuditCreatedOffset
-------------------  --------------------------
2024-01-01 12:00:00  2024-01-01 04:00:00 -08:00
2024-05-01 12:00:00  2024-05-01 05:00:00 -07:00
2024-12-01 12:00:00  2024-12-01 04:00:00 -08:00

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

此部分介绍了在 datetimeoffset 数据类型转换为其他日期和时间数据类型时发生的具体情况。

转换为 日期时,将复制年份、月和日。 以下代码显示将 datetimeoffset(4) 值转换为日期值的结果。

DECLARE @datetimeoffset DATETIMEOFFSET(4) = '12-10-25 12:32:10 +01:00';
DECLARE @date DATE = @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset', @date AS 'date';

下面是结果集:

@datetimeoffset                 date
------------------------------ ----------
2025-12-10 12:32:10.0000 +01:0 2025-12-10

如果转换为 time(n),则会复制小时、分钟、秒和小数秒。 时区值被截断。 当 datetimeoffset(n) 值的精度大于 time(n 值的精度时,将向上舍入该值。 以下代码显示将 datetimeoffset(4) 值转换为 time(3) 值的结果。

DECLARE @datetimeoffset DATETIMEOFFSET(4) = '12-10-25 12:32:10.1237 +01:0';
DECLARE @time TIME(3) = @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset ', @time AS 'time';

下面是结果集:

@datetimeoffset                 time
------------------------------- ------------
2025-12-10 12:32:10.1237 +01:00 12:32:10.124

转换为 datetime 时,将复制日期和时间值,并截断时区。 当 datetimeoffset(n 值的分数精度大于三位数时,该值将被截断。 以下代码显示将 datetimeoffset(4) 值转换为日期/时间值的结果。

DECLARE @datetimeoffset DATETIMEOFFSET(4) = '12-10-25 12:32:10.1237 +01:0';
DECLARE @datetime DATETIME = @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime';

下面是结果集:

@datetimeoffset                datetime
------------------------------ -----------------------
2025-12-10 12:32:10.1237 +01:0 2025-12-10 12:32:10.123

转换成 smalldatetime 时,会复制日期和小时。 分钟数会根据秒值向上舍入,秒数设置为 0。 以下代码显示将 datetimeoffset(3) 值转换为 smalldatetime 值的结果。

DECLARE @datetimeoffset DATETIMEOFFSET(3) = '1912-10-25 12:24:32 +10:0';
DECLARE @smalldatetime SMALLDATETIME = @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset', @smalldatetime AS '@smalldatetime';

下面是结果集:

@datetimeoffset                @smalldatetime
------------------------------ -----------------------
1912-10-25 12:24:32.000 +10:00 1912-10-25 12:25:00

如果转换为 datetime2(n),则日期和时间将 复制到 datetime2 值,并截断时区。 当 datetime2(n) 值的精度大于 datetimeoffset(n 值的精度时,小数秒将被截断以适应。 以下代码显示将 datetimeoffset(4) 值转换为 datetime2(3) 值的结果。

DECLARE @datetimeoffset DATETIMEOFFSET(4) = '1912-10-25 12:24:32.1277 +10:0';
DECLARE @datetime2 DATETIME2(3) = @datetimeoffset;

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

下面是结果集:

@datetimeoffset                    @datetime2
---------------------------------- ----------------------
1912-10-25 12:24:32.1277 +10:00    1912-10-25 12:24:32.12

将字符串文本转换为 datetimeoffset

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

输入字符串文字 datetimeoffset(n
ODBC DATE ODBC 字符串文字映射到 datetime 数据类型。 从ODBC DATETIME文本到 datetimeoffset 类型的任何赋值操作都会导致日期/时间与此类型之间的隐式转换(由转换规则定义)。
ODBC TIME 请参阅上一 ODBC DATE 规则
ODBC DATETIME 请参阅上一 ODBC DATE 规则
DATE 部件 TIME 默认为 00:00:00. 默认值TIMEZONE+00:00
TIME 部件 DATE 默认为 1900-1-1. 默认值TIMEZONE+00:00
TIMEZONE 提供默认值
DATE + TIME 默认值TIMEZONE+00:00
DATE + TIMEZONE 不允许
TIME + TIMEZONE DATE 部分默认为 1900-1-1
DATE + TIME + TIMEZONE 无庸赘述