DATEDIFF SQL Server 2016 JSON

Question

Currently have a startDate and EndDate formatted in JSON and trying to use a DATEDIFF function to work out the year difference using OPENJSON.

I am currently trying the below

DECLARE @Json VARCHAR(4000) = '
{
  "Name": "bob",
  "StartDate": "12/02/2015",
  "EndDate": "12/02/2016"
}';



SELECT  Name ,
        StartDate ,
        EndDate
FROM    OPENJSON(@Json, '$')
WITH (Name VARCHAR(50), StartDate DATETIME2, EndDate DATETIME2
,DATEDIFF(YEAR, StartDate DATETIME2, EndDate DATETIME2) INT AS Duration
)
WHERE Name = 'bob'

However, I get error message:

Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '('.

Anyone know if it is possible to do what I am doing? Or propose another way? I'd like to avoid creating a temp table, inserting into it and then reading from it with the DATEDIFF on the select if possible.

Answer 1

I've not used OPENJSON, but shouldn't the WITH section contain the definition of the Duration column as opposed to the DATEDIFF and then move the DATEDIFF to the SELECT. Like so:

DECLARE @Json VARCHAR(4000) = '
{
  "Name": "bob",
  "StartDate": "12/02/2015",
  "EndDate": "12/02/2016"
}';

SELECT  Name ,
        StartDate ,
        EndDate ,
        DATEDIFF(YEAR, StartDate, EndDate) AS Duration
FROM    OPENJSON(@Json, '$')
WITH (Name VARCHAR(50), StartDate DATETIME2, EndDate DATETIME2, Duration INT
)
WHERE Name = 'bob'

Or just do the DATEDIFF in the SELECT:

SELECT  Name ,
        StartDate ,
        EndDate ,
        DATEDIFF(YEAR, StartDate, EndDate) AS Duration
FROM    OPENJSON(@Json, '$')
WITH (Name VARCHAR(50), StartDate DATETIME2, EndDate DATETIME2)
WHERE Name = 'bob'