JSON date times converting to SQL data types

JSON date time in SQL .sql

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

-- 7 zeroes after the decimal? Our source only had 3 zeroes!
-- Since JSON/JavaScript times have decimal precision to only 3 places, we need to make
-- the precision of datetime2 match
SELECT CONVERT(datetime2(3), JSON_VALUE(@jsonData, '$.createDate'))
-- Output: 2017-03-28 12:45:00.000

-- So now we are returning our UTC date time from JSON, but what if we need to convert it to a different time zone?
-- Using SQL Server 2016's AT TIME ZONE with CONVERT() will allow us to do that easily.
-- To get a full list of time zone names, you can use SELECT * FROM sys.time_zone_info
SELECT CONVERT(datetime2(3), JSON_VALUE(@jsonData, '$.createDate')) AT TIME ZONE 'Eastern Standard Time'
-- Output: 2017-03-28 12:45:00.000 -04:00

-- What if we just need to grab the date?  Pretty easy, just CONVERT() to date
SELECT CONVERT(date, JSON_VALUE(@jsonData, '$.createDate'))
-- Output: 2017-03-28

--Same with just the time, just remember to use a precision value of 3
SELECT CONVERT(time(3), JSON_VALUE(@jsonData, '$.createDate'))
-- Output: 12:45:00.000

Hi Bert,
Thanks for the above. I've been searching for this for a while. One question, how do I use this when calling an openrowset? I'm reading in a number of data and one of them requires conversion from Java DateTime to SQL DateTime. See below, as I'm unsure as to syntax here

Thanks for any help:

Select temp. *
--into tempTable
from openrowset(bulk 'C:..\ICB_GM.json', Single_Clob) as DeptJSON
Cross Apply openjson(BulkColumn)

With
(
-- fieldName DataType [Path]
orgID int '$.organization.id',
routeID int '$.key',
deliveryDate date '$.date',
routeDeparture NVARChar(50) '$.actualDeparture',
routeDepDateTime datetime CONVERT(datetime2(3), JSON_VALUE(DeptJSON, '$.actualDeparture')) ,
) As temp