bertwagner

static void Main(string[] args)
        {
            string cars = @"[ {""year"":2001,""make"":""ACURA"",""model"":""CL""}, ... ]";
            Stopwatch stopwatch = new Stopwatch();

            // Test #1
            stopwatch.Start();
            var deserializedCars = JsonConvert.DeserializeObject<IEnumerable<Car>>(cars);
            stopwatch.Stop();
            long elapsedMillisecondsDeserialize = stopwatch.ElapsedMilliseconds;

bertwagner

DECLARE @jsonData nvarchar(max) = N'{ "createDate" : "2017-03-28T12:45:00.000Z" }'

-- SQL's JSON_VALUE() will read in the JSON date time as a string
SELECT JSON_VALUE(@jsonData, '$.createDate')
-- Output: 2017-03-28T12:45:00Z

-- If we want to read it in as a SQL datetime2, we need to use a CONVERT() (or a CAST())
SELECT CONVERT(datetime2, JSON_VALUE(@jsonData, '$.createDate'))
-- Output: 2017-03-28 12:45:00.0000000

bertwagner

DECLARE @sqlData datetime2 = '2017-03-28 12:45:00.1234567'

-- Let's first try the simplest SQL to JSON conversion first using FOR JSON PATH
SELECT @sqlData as SQLDateTime2 FOR JSON PATH
-- Output: [{"SQLDateTime2":"2017-03-28T12:45:00"}]

-- Honestly that's not too bad!
-- The datetime gets created in the YYYY-MM-DDTHH:MM:SS.fffffff format
-- Although this is pretty much what we need, what if we want to be explicit and specify that we are in UTC?
-- Just add the AT TIME ZONE modifier and we will get our JSON "Z" indicating UTC

bertwagner

DECLARE @sqlDate datetime2 = '2017-03-28 12:45:00.1234567'

 

DECLARE @jsonData nvarchar(max) = N'{ "createDate" : "2017-03-28T12:45:00.000Z" }'
		,@newDate datetime2(3) = '2017-03-28T12:48:00.123Z'

-- Let's start out modifying our data by replacing the value completely
SELECT JSON_VALUE(@jsonData, '$.createDate')

bertwagner

USE Sandbox;

DROP TABLE IF EXISTS dbo.XmlVsJSON
CREATE TABLE dbo.XmlVsJson
(
	Id INT IDENTITY PRIMARY KEY,
	XmlData XML,
	JsonData NVARCHAR(MAX)
)

bertwagner

DROP TABLE IF EXISTS dbo.XmlVsJSON2
CREATE TABLE dbo.XmlVsJson2
(
	Id INT IDENTITY PRIMARY KEY,
	XmlData XML,
	JsonData NVARCHAR(MAX)
)

bertwagner

-- Create our test data: A table of a car dealer's inventory of cars
-- car data source: https://github.com/arthurkao/vehicle-make-model-data
DROP TABLE IF EXISTS dbo.AllCarsInOneRow
CREATE TABLE dbo.AllCarsInOneRow
(
	Id INT IDENTITY(1,1),
	CarDetails NVARCHAR(MAX)
);
DROP TABLE IF EXISTS dbo.XmlVsJson
CREATE TABLE dbo.XmlVsJson

bertwagner

using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Serialization;

bertwagner

-- Drop and create our temporal and historical tables
IF OBJECT_ID('dbo.CarInventory', 'U') IS NOT NULL 
BEGIN
	-- When deleting a temporal table, we need to first turn versioning off
	ALTER TABLE dbo.CarInventory SET ( SYSTEM_VERSIONING = OFF  ) 
	DROP TABLE dbo.CarInventory
	DROP TABLE dbo.CarInventoryHistory
END;
CREATE TABLE CarInventory   
(    

bertwagner

-- Test data taken from my favorite green coffee seller's website, Sweet Maria's https://www.sweetmarias.com/category/green-coffee
DROP TABLE IF EXISTS dbo.CoffeeInventory
CREATE TABLE dbo.CoffeeInventory
(
	ID int IDENTITY(1,1),
	Name VARCHAR(100),
	Price VARCHAR(5),--DECIMAL(4,2),
	Description VARCHAR(500),
	CreateDate DATETIME2 DEFAULT GETDATE()
)