Question: Is it possible to change the values of sub-json-data in the following manner?

```
DECLARE @json NVARCHAR(MAX) = "{"message":"Machine is down","machineId":"165ACE37-4E2C-4D44-9D14-F9E2CB2C2C13","machineName":"1501","ipAddress":"192.168.150.101","time":"2018-05-20T18:33:23.171"}"

SELECT * FROM OPENJSON(@json) 
WITH (  message varchar(200) '$.message',
    machineId varchar(200) '$.machineId',
    machineName int '$.machineName',
    ipAddress varchar(200) '$.ipAddress',
    LocalTime datetime2(7) '$.time'
) AS ChangeTime

```

I want to change the LocalTime variable i created and then replace the current one.

I want to remove two hours from the $.time parameter. 

Something like this: 

```
UPDATE Table SET ChangeTime.LocalTime = DATEADD(hour,-2,ChangeTime.LocalTime)

```

Input: `" time":"2018-05-20T18:33:23.171"`

output:
`"time":"2018-05-20T16:33:23.171"`

How would i accomplish this?

Answer: 
You could use [`JSON_MODIFY`](https://learn.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql?view=sql-server-2017):

> 
> Updates the value of a property in a JSON string and returns the updated JSON string. 
> 
> 
> 

```
SET @json=JSON_MODIFY(@json,'$.time',
   FORMAT(DATEADD(hour,-2,JSON_VALUE(@json,'$.time')),'yyyy-MM-ddTHH:mm:ss.fff'));

SELECT * FROM OPENJSON(@json) 
WITH (  message varchar(200) '$.message',
    machineId varchar(200) '$.machineId',
    machineName int '$.machineName',
    ipAddress varchar(200) '$.ipAddress',
    LocalTime datetime2(7) '$.time'
) AS ChangeTime;

```

**[DBFIddle Demo](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=13cb42cbc998e84b0cabdd36542e3c28)**
Maybe something like this:

```
LocalTime - INTERVAL '2 hours'

```

With interval you can remove or add any time denomination. Like days, months, years, seconds etc

```
WITH (  message varchar(200) '$.message',
    machineId varchar(200) '$.machineId',
    machineName int '$.machineName',
    ipAddress varchar(200) '$.ipAddress',
    LocalTime datetime2(7) '$.time'
) AS ChangeTime
SELECT LocalTime - INTERVAL '2 hours'
FROM ChangeTime

```