DATEDIFF SQL Server 2016 JSON [英] DATEDIFF SQL Server 2016 JSON

问题描述

当前具有以JSON格式设置的startDate和EndDate，并尝试使用DATEDIFF函数通过OPENJSON来计算年份差异.

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

我目前正在尝试以下

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:

第15级，州1，第15行的消息102，
'('附近的语法不正确.

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

有人知道我可以做什么吗?还是提出另一种方式?我想避免创建临时表，将其插入表中，然后在可能的情况下使用select上的DATEDIFF从表中读取.

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.

推荐答案

我没有使用OPENJSON，但WITH部分不应包含Duration列的定义，而不是，然后将DATEDIFF移至SELECT.像这样:

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'

或者只是在SELECT中执行DATEDIFF:

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

这篇关于DATEDIFF SQL Server 2016 JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！