在SQL Server FOR JSON PATH中将日期转换为ISO 8601

Question

我正在SQL Server中将数据转换为json(使用FOR JSON PATH(，该数据中的一列具有datetime数据类型，我需要将其转换为ISO8601

正确格式：2018-11-13T20:20:39+00:00

当我尝试CONVERT(NVARCHAR(33), datetimecolumn, 127)时，结果是：2019-10-24T12:35:12.870

当我尝试TODATETIMEOFFSET(datetimecolumn, '+00:00')时，结果是：2015-03-31T00:00:00Z

在这两种情况下，我都没有得到+00:00。

Answer 1

SQL Server似乎用Z表示零偏移或UTC时间。

如果您运行SELECT TODATETIMEOFFSET(GETDATE(),60)，它获取当前时间并应用1小时的偏移量，那么您将获得所需的格式。然而，我认为Z表示UTC在ISO8601中是完全可以接受的，但可能是错误的。

此命令似乎按预期输出JSON。

SELECT TODATETIMEOFFSET(GETDATE(),60) as T FOR JSON PATH

如果将零偏移量表示为00:00是至关重要的，我相信您将被迫使用字符串。

select CAST(CONVERT(NVARCHAR(33), getdate(), 127) as NVARCHAR(35)) + '+00:00' as T FOR JSON PATH

Answer 2

JSON(作为一种数据交换格式(没有指定日期和时间值的格式。FOR JSON隐式地将日期和时间值转换为字符串，但在使用FOR JSON:之前，您可以在语句中使用适当的类型和样式强制转换datetime值

声明：

SELECT CONVERT(varchar(30), CONVERT(datetimeoffset(0), GETDATE()), 126) AS T
FOR JSON PATH

结果：

[{"T":"2020-04-22T08:02:58+00:00"}]

如果您想转换到特定的时区，您可以尝试使用AT TIME ZONE:

声明：

SELECT CONVERT(nvarchar, CONVERT(datetime2(0), GETDATE()) AT TIME ZONE 'Central European Standard Time', 126) AS T 
FOR JSON PATH

结果：

[{"T":"2020-04-24T08:33:49+02:00"}]