stacki stacki - 2 months ago 27
JSON Question

SQL Server 2012 get results as JSON

My select returns 02:18:07

;WITH DEMO(ID, FromTime, ToTime) AS
(
SELECT TOP 1
ID
,CONVERT(DATETIME,FromTime,120) AS FromTime
,CONVERT(DATETIME,ToTime,120) AS ToTime
FROM (VALUES (1,'2016-09-03 18:14:47.000','2016-09-03 20:32:54.000')
,(2,'2016-09-03 12:35:45.000','2016-09-03 15:06:06.000')
) AS X(ID,FromTime,ToTime)
)
SELECT
DATEADD(SECOND,SUM(DATEDIFF(SECOND, k.FromTime, k.ToTime)) OVER
(
PARTITION BY (SELECT NULL)
),CONVERT(TIME(0),'00:00:00',0)) AS TIME_SUM
FROM DEMO k;


how get this result as "{\"returnDate\" : [ {\"TIME_SUM\":\"02:18:07\"}]} " ?

Answer

There is not enough detail on what you are trying to achieve; if you really want that output only for one value shown in the question, an ugly solution is here:

;WITH DEMO(ID, FromTime, ToTime) AS
(
    SELECT TOP 1
        ID
       ,CONVERT(DATETIME,FromTime,120) AS FromTime
       ,CONVERT(DATETIME,ToTime,120) AS ToTime
    FROM (VALUES (1,'2016-09-03 18:14:47.000','2016-09-03 20:32:54.000')
                ,(2,'2016-09-03 12:35:45.000','2016-09-03 15:06:06.000')
    ) AS X(ID,FromTime,ToTime)
)
SELECT
    '{\"returnDate\" : [ {\"TIME_SUM\":\"' + 
     CONVERT(VARCHAR(20),DATEADD(SECOND,SUM(DATEDIFF(SECOND, k.FromTime, k.ToTime)) OVER 
     (
            PARTITION BY (SELECT NULL)
     ),CONVERT(TIME(0),'00:00:00',0))) + '\"}]}' AS TIME_SUM
FROM DEMO k