如何将带时区的JSON日期转换为SQL Server日期时间?

Question

我将JSON对象的输出保存在CSV文件中。我想把数据导入SQL Server。我已经尝试将JSON日期列转换为SQL Server datetime数据类型。

select cast('2009-06-18T16:44:20+0000' as datetime)

这会引发一个错误:从字符串转换日期和/或时间时转换失败。

如何将带时区的JSON日期转换为SQL Server日期时间?

Answer 1

据我所知，SQL Server用冒号识别时区。您必须按如下方式重新格式化时区部分:

SELECT CONVERT(datetime2, STUFF('2009-06-18T16:44:20+0000', 23, 0, ':'))

根据MSDN ISO 8601有YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]格式

Answer 2

我有一个类似的问题;我得到了同样的错误，虽然日期格式为我的日期是有点不同。我的解决方案是将其转换为DATETIME2值，而不仅仅是DATETIME。如果我没有看到Pawel的答案，我就不会尝试转换为DATETIME2。

我得到的日期来自一个c#对象，该对象使用JSON.Net序列化为JSON。下面是一个示例SQL脚本，展示了datetime值是什么以及如何转换它。

DECLARE @effectiveDateJSON VARCHAR(MAX) = 
'{"EffectiveDate":"2017-02-07T00:00:00-06:00", "CreateDate":"2017-02-07T16:32:12.9130892-06:00"}';
SELECT *
FROM OPENJSON(@effectiveDateJSON)
WITH (EffectiveDate DATETIME2 '$.EffectiveDate',
    CreateDate DATETIME2 '$.CreateDate'
);

Answer 3

对于时区感知转换

declare @offset datetimeoffset = JSON_VALUE('{"d": "2021-01-01T00:00:00+03:00"}', '$.d')
declare @inCurrentTimezone datetime = @offset AT TIME ZONE CURRENT_TIMEZONE_ID()
select @inCurrentTimezone

Docs:AT TIME ZONE, datetimeoffset