如何将带时区的JSON日期转换为SQL Server日期时间? [英] How can I convert a JSON date with timezone to a SQL Server datetime?

问题描述

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

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

这将引发错误:从字符串转换日期和/或时间时转换失败.

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

解决方案

据我所知，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]格式. /p>

I saved the output of a JSON object in a CSV file. I want to import the data into SQL Server. I have tried to cast the JSON date column to a SQL Server datetime data type.

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

This causes an error to be raised: Conversion failed when converting date and/or time from character string.

How can I convert a JSON date with timezone to a SQL Server datetime?

解决方案

As far as I know, SQL Server recognizes timezone with colon. You have to reformat timezone part as follows:

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

According to MSDN ISO 8601 has YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm] format.

这篇关于如何将带时区的JSON日期转换为SQL Server日期时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！