datetimeoffset (Transact-SQL)

项目
11/19/2024

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

定义一个日期，该日期与基于 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-01` 到 `9999-12-31` 公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日
时间范围	`00:00:00` 到 `23:59:59.9999999`
时区偏移范围	`-14:00` 到 `+14:00`
元素范围	`yyyy`是四位数字，范围从`00019999`表示一年。 `MM` 是两个数字，范围从一 `01` 到 `12`，表示指定年份中的一个月。 `dd` 是两个数字，范围从月份 `01` 到 `31` 根据月份，表示指定月份的一天。 `HH` 是表示小时数的两位数字，范围介于 `00` 1 到 `23`2 位。 `mm` 是表示分钟数的两位数字，范围从 `00` 1 到 `59`1。 `ss` 是两个数字，范围从 `00` 1 到 `59`，表示第二个数字。 n 为零到七位数字，范围从零 `0` 到 `9999999`7 位，表示小数秒。 `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';

结果集如下。

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`
`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_WVARCHAR` 或 `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` 或 `SqString`
date	yyyy-MM-dd	`SQL_WVARCHAR` 或 `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` 或 `SqString`
datetime2	yyyy-MM-dd HH：mm：ss[.nnnnnnn]	`SQL_WVARCHAR` 或 `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` 或 `SqString`
datetimeoffset	yyyy-MM-dd HH：mm：ss[.nnnnnnnnn] [+\|-]hh：mm	`SQL_WVARCHAR` 或 `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` 或 `SqString`

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

在 Microsoft Fabric SQL 数据库中：可以使用 7 位数字的精度，但 Fabric OneLake 中的镜像数据将具有时区和第七次小数剪裁。此列类型不能用作 Fabric SQL 数据库中表的主键。

转换日期和时间数据

转换为日期和时间数据类型时，SQL Server 将拒绝无法识别为日期或时间的所有值。有关对日期和时间数据使用 CAST 和 CONVERT 函数的信息，请参阅 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`	无庸赘述

通过

datetimeoffset (Transact-SQL)

datetimeoffset 说明

datetimeoffset 支持的字符串文字格式

时区偏移量

时区转换语法

对 ANSI 和 ISO 8601 的遵从性

下级客户端的向后兼容性

Microsoft Fabric 支持

转换日期和时间数据

转换为 datetimeoffset 数据类型

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

将字符串文本转换为 datetimeoffset

反馈

其他资源

通过

datetimeoffset (Transact-SQL)

datetimeoffset 说明

datetimeoffset 支持的字符串文字格式

时区偏移量

时区转换语法

对 ANSI 和 ISO 8601 的遵从性

下级客户端的向后兼容性

Microsoft Fabric 支持

转换日期和时间数据

转换为 datetimeoffset 数据类型

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

将字符串文本转换为 datetimeoffset

相关内容

反馈

其他资源