Menu
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?
520
The way to solve this is to use the (slightly adapted) ISO-8601 date format that is supported by SQL Server - this format works always - regardless of your SQL Server language and dateformat settings.
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. You need separators for the date like a “/”, a “.” or a “-“. We use substring to concatenate the “-” to use an acceptable date format and then we use the CONVERT function to convert the characters to sql date.
OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns. In other words, OPENJSON provides a rowset view over a JSON document. You can explicitly specify the columns in the rowset and the JSON property paths used to populate the columns.
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
63
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
