datetimeoffset (Transact-SQL)

项目
10/31/2024

适用于： 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-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

转换日期和时间数据

转换为日期和时间数据类型时，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

相关内容

反馈

其他资源