Tarzan Tarzan - 3 months ago 12
JSON Question

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

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?

Answer

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.