Published on

Notes on using MS SQl and JSON

Authors
  • avatar
    Name
    Timothy Blanks
    -- get child json as clean json
    select ID
        ,JSON_QUERY(data) AS Data
    from jsontest
    WHERE id = 61685
    FOR JSON AUTO

Add the current UTC date Time into a json variable

    SET @prop = JSON_MODIFY(@prop, '$.x.LastModifiedDateTime', CONVERT(NVARCHAR,GETUTCDATE(),127) +'Z' )

Convert a date stored in JSON to UTC

    select cast(JSON_VALUE(JSONCol, '$.x.LastModifiedDateTime') AS DATETIME2) AS LastMod
    from table