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

inputData is jsonfile.... id and Assignee inserted properly into database but when i tried to insert created date into database it throws error Conversion failed when converting date and/or time from character string. — Pallavi, Jun 16 '21 at 06:06
inputData json file is: {"expand":"operations,versionedRepresentations,editmeta,changelog,renderedFields","id":"180","fields":{"created":"2021-03-04T07:11:40.000+0000","assignee":{"name":"pallavi"}} — Pallavi, Jun 16 '21 at 07:29
Please [Edit](https://stackoverflow.com/posts/67997001/edit) your question to include the JSON data. It looks incomplete, missing at least the closing `}` character. — AlwaysLearning, Jun 16 '21 at 09:46

score 0 · Accepted Answer · answered Jun 16 '21 at 10:00

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;

How to insert date from JSON to SQL database

1 Answers1