sql-serverjsonazure-sql-database

datetime conversion fails in OPENJSON function

I am using a json coming from logicapp events which has datetime properties in below format

"startTime": "2017-07-12T17:14:16.0500772Z",
"endTime": "2017-07-12T17:14:17.2939322Z",

in a stored proc with the OPENJSON Sql function. When I execute the 'SELECT' with this function it fails with this error

Msg 241, Level 16, State 1, Line 33 Conversion failed when converting date and/or time from character string.

My observation has been that OPENJSON fails to convert the millisec part after the 3rd digit.

Question Has anyone seen such a problem with OPENJSON SQL function?

Solution

  • If DateTime2, you should have no issue

    Example

    select AsDT2 = try_convert(datetime2,'2017-07-12T17:14:16.0500772Z')
      ,AsDT  = try_convert(datetime, '2017-07-12T17:14:16.0500772Z')

    Returns

    AsDT2                         AsDT
2017-07-12 17:14:16.0500772   NULL   --<< Fails DateTime conversion

    The Actual Conversion

    Declare @JSON varchar(max) = '{"startTime": "2017-07-12T17:14:16.0500772Z","endTime": "2017-07-12T17:14:17.2939322Z"}'

SELECT * FROM  
 OPENJSON (@JSON )  
WITH (   
              startTime  datetime2 '$.startTime',  
              endTime    datetime2 '$.endTime'
 )

    See It In Action dbFiddle