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

Kevin Bonnett 21 Reputation points
2021-11-19T21:13:13.557+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,509 questions
{count} votes

Accepted answer
  1. Viorel 82,661 Reputation points
    2021-11-21T06:32:57.46+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. Viorel 82,661 Reputation points
    2021-11-19T21:49:29.367+00:00

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