Datediff Sql Server 2016 Json

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 DECLA

Solution 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@JsonVARCHAR(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'

Solution 2:

Try one of following solutions (untested):

SELECT  s.*, Duration = DATEDIFF(YEAR, StartDate, EndDate)
FROM (
    SELECT  Name ,
            StartDate   =CONVERT(DATE, StartDateChar, 103),
            EndDate     =CONVERT(DATE, EndDateChar, 103)
    FROM    OPENJSON(@Json, '$') WITH (Name VARCHAR(50), StartDateChar VARCHAR(10), EndDateChar VARCHAR(10))
    WHERE   Name ='bob'
) AS s

or

SELECT*, Duration = DATEDIFF(YEAR, StartDate, EndDate)
FROM    OPENJSON(@Json, '$') WITH (Name VARCHAR(50), StartDateChar VARCHAR(10), EndDateChar VARCHAR(10))
CROSS APPLY (
    SELECT          
        StartDate   =CONVERT(DATE, StartDateChar, 103),
        EndDate     =CONVERT(DATE, EndDateChar, 103)
) x
WHERE   Name ='bob'