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-01 到 9999-12-31 公元 1 年 1 月 1 日到公元 9999 年 12 月 31 日 |
时间范围 | 00:00:00 到 23:59:59.9999999 |
时区偏移范围 | -14:00 到 +14:00 |
元素范围 | yyyy 是四位数字,范围从0001 9999 表示一年。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';
下面是结果集。
数据类型 | 输出 |
---|---|
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
是两位数,范围介于00
14
时区偏移量和表示小时数。mm
是两个数字,范围为00
59
2,表示时区偏移量中的其他分钟数。+
(加)或-
(减)是时区偏移量的必需符号。 此符号指示是否从 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 时区编写的。
在语句中UPDATE
AT 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 |
无庸赘述 |