SQL OPENJSON datetime2 conversion fails from JSON object (Syntax issue)

Question

Hello everyone. I am having an issue converting a json string into a SQL column with a type of DATETIME2. I have tried to use the SQL CONVERT function but I either encounter SQL syntax errors, or get the conversion error whil using open json.

My JSON data looks like this:

{"time":"01 Jan 2011 00:00 UT", "phase":12.35, "age":26.267, "diameter":1889.5, "distance":379317, "j2000":{"ra":15.7450, "dec":-22.8045}, "subsolar":{"lon":-134.198, "lat":0.242}, "subearth":{"lon":4.820, "lat":3.733}, "posangle":14.098}

As you can see the "time" key value as a rather unique format which I believe is the problem. I tried to declare this field initially as a NAVCHAR(17) which crops off the "UT" part of the JSON value. The conversion from this also failed for me as I am sure I am not implementing correctly.

My code to read the JSON looks like this:

DECLARE @json NVARCHAR(MAX) =
(SELECT BulkColumn
FROM OPENROWSET (BULK 'C:\Users\kbonn\Documents\mooninfo_2011.json', SINGLE_CLOB) AS json)

If (ISJSON(@json )=1)
SELECT @json AS 'JSON Text'

SELECT *
FROM OPENJSON(@json )

WITH (  
		[Time] DATETIME2 '$.time',  
		Phase DECIMAL '$.phase',   
		Age DECIMAL '$.age',  
		Diameter DECIMAL '$.diameter',  
		Distance DECIMAL '$.distance',  
		Right_Ascension FLOAT(2) '$.j2000.ra',  
		Declination FLOAT(2) '$.j2000.dec',  
		Subsolar_Longitude FLOAT(2) '$.subsolar.lon',  
		Subsolar_Latitude FLOAT(2) '$.subsolar.lat',  
		Subearth_Longiude FLOAT(1) '$.subearth.lon',  
		Subearth_Latitude FLOAT(1) '$.subearth.lat',  
		Position_Angle FLOAT '$.posangle'  
		  
		)  

Running this code produces this error:

(1 row affected)
Msg 241, Level 16, State 1, Line 8
Conversion failed when converting date and/or time from character string.

Note: If I change the TIME column datatype to NVARCHAR(17) all the data loads in the table, but I need the column to be at least a DATETIME type or preferably the DATETIME2 type.

Thank you in advance for taking the time to consider this and offer your guidance. I do not believe this is a duplicate question because my struggle seems to be a syntax issue where I cannot get the conversion code in the proper place.

Answer 1

Try something like this:

SELECT 
    convert(datetime2, [Time], 113) as [Time],
    Phase,
    Age,
    Diameter,
    Distance,
    Right_Ascension,
    Declination,
    Subsolar_Longitude,
    Subsolar_Latitude,
    Subearth_Longiude,
    Subearth_Latitude,
    Position_Angle
FROM OPENJSON(@JSON)
WITH (
         [Time] nvarchar(17) '$.time',
         Phase DECIMAL '$.phase', 
         Age DECIMAL '$.age',
         Diameter DECIMAL '$.diameter',
         Distance DECIMAL '$.distance',
         Right_Ascension FLOAT(2) '$.j2000.ra',
         Declination FLOAT(2) '$.j2000.dec',
         Subsolar_Longitude FLOAT(2) '$.subsolar.lon',
         Subsolar_Latitude FLOAT(2) '$.subsolar.lat',
         Subearth_Longiude FLOAT(1) '$.subearth.lon',
         Subearth_Latitude FLOAT(1) '$.subearth.lat',
         Position_Angle FLOAT '$.posangle'
     )
 where cast([Time] as date) = '2011-01-01'

For new aspects, you can start a new question, where you can write a larger code or attach text files.

Answer 2

After cropping "UT" using NVARCHAR(17), maybe you should use a CONVERT like this: convert(datetime2, '01 Jan 2011 00:00', 113).