How to insert date from JSON to SQL database

Question

I have to insert some data from JSON to SQL database so I use below code:

sqlQuery1 = "DECLARE @json varchar(max)='" + inputData + "';";
sqlQuery2 = "INSERT INTO [Test].[dbo].[Work] " +
                        "SELECT [Id], [Created], [Assignee] " +
                        "FROM OPENJSON(@json) "+
                        "WITH ([Id] int '$.id',"+
                        "[Created] datetimeoffset(4) '$.fields.created',"+
                       "[Assignee] varchar(200) '$.fields.assignee.name')";
        
System.out.println(sqlQuery2); stmt.addBatch(sqlQuery1);stmt.addBatch(sqlQuery2);                    break;

$fields.created date has format eg: "2021-03-04T07:11:40.000+0000" I tried using different way but not able to insert above format date into SQL. Kindly help me with this code to insert created date to db.

Thank you in advance

Answer 1

SQL Server expects the time zone portion of your datetimeoffset string to include the colon character, i.e.: +00:00 instead of just +0000.

You will need to extract it from JSON as a varchar/nvarchar column first so that you can insert the : character before converting it to a datetimeoffset like so:

declare @json nvarchar(max) = N'{
    "expand": "operations,versionedRepresentations,editmeta,changelog,renderedFields",
    "id": "180",
    "fields": {
        "created": "2021-03-04T07:11:40.000+0000",
        "assignee": {
            "name": "pallavi"
        }
    }
}';

select *
from openjson(@json) with (
  [Id] int '$.id',
  [Created] varchar(29) '$.fields.created',
  [Assignee] varchar(200) '$.fields.assignee.name'
) shredded
outer apply (
  select [CreatedDatetimeoffset] = convert(datetimeoffset, stuff(Created, 27, 0, ':'))
) converted;