JSON Support for SQL Server

Documentation › JsonDateTime

JsonDateTime(data, query, format) or JsonDataType QueryDateTime(query, format)

Query a DateTime value stored as text within a JSON string.

Example

JsonDateTime(data, query, format)

JsonDataType QueryDateTime(query, format)

declare @orderData nvarchar(1000) set @orderData = '{ "Name": "Bob Smith", "ItemCount": 12, "Total": 15.54, "Packed": true, "Shipped": false, "OrderDate": "2015-02-10 14:52:23", "Lines": [ { "Item": "Hat", "Quantity": 10, "UnitPrice": 1.11, "Total": 11.1 }, { "Item": "Scarf", "Quantity": 2, "UnitPrice": 2.22, "Total": 4.44 } ] }' select dbo.JsonDateTime(@orderData, 'OrderDate', 'yyyy-MM-dd HH:mm:ss') as [Order Date]

Results

Order Date
2015-02-10 14:52:23.000

JsonDataType QueryDateTime(query, format)

declare @orderData json set @orderData = '{ "Name": "Bob Smith", "ItemCount": 12, "Total": 15.54, "Packed": true, "Shipped": false, "OrderDate": "2015-02-10 14:52:23", "Lines": [ { "Item": "Hat", "Quantity": 10, "UnitPrice": 1.11, "Total": 11.1 }, { "Item": "Scarf", "Quantity": 2, "UnitPrice": 2.22, "Total": 4.44 } ] }' select @orderData.QueryDateTime('OrderDate', 'yyyy-MM-dd HH:mm:ss') as [Order Date]

Results

Order Date
2015-02-10 14:52:23.000