CODEWITHSUNDEEP
sql-serverjsonazure-sql-database
TusharJ
TusharJ

Reputation: 1263

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?

Upvotes: 8

Views: 5691

Answers (2)

Amirreza
Amirreza

Reputation: 663

for me it was comparison order

my json:

declare @advanceSearch nvarchar(max) = N'[{"field":"title","op":"like","val":"a"},{"field":"RegDate","op":"<=","val":"2024-07-12"}]'

and where query:

where searchField = 'RegDate' and RegDate < searchValue)

but it was first comparing value (second part) before checking first part

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 11

Related Questions