'OutputDataConversionError.TypeConversionError' when inserting data into Azure SQL Database table from Azure Stream Analytics

Question

I am trying to learn Azure IoT. What i am trying to is to send MQTT message to IoT Hub. And from IoT hub, i am using Streaming Analytics to output the data into SQL Database. But currently in Streaming Analytics Output, i have this error:

[9:12:30 AM] Source 'OUTPUTSQL' had 1 occurrences of kind 'OutputDataConversionError.TypeConversionError' between processing times '2019-05-23T01:12:30.5631321Z' and '2019-05-23T01:12:30.5631321Z'.

This is the data that i am trying to save

{"ActionBy":"admin","ActionDate":"2019-05-22T16:00:00.0000000","Topic":"IoT","Message":"Hello World","QoS":"1","EventProcessedUtcTime":"2019-05-23T00:19:31.8287610Z","PartitionId":0,"EventEnqueuedUtcTime":"2019-05-23T00:19:31.7170000Z","IoTHub":{"MessageId":null,"CorrelationId":null,"ConnectionDeviceId":"hartaMQTT-Device01","ConnectionDeviceGenerationId":"636936650733289038","EnqueuedTime":"2019-05-23T00:19:31.6820000Z","StreamId":null}}

I think the error is in IoTHub part.

This is the table in Azure Sql Server that i am trying to save:

CREATE TABLE [dbo].[IoTMQTT](
[EventID] [bigint] IDENTITY(1,1) NOT NULL,
[ActionBy] [nvarchar](400) NOT NULL,
[ActionDate] [datetime] NOT NULL,
[Topic] [nvarchar](400) NULL,
[Message] [nvarchar](400) NULL,
[QoS] [nvarchar](400) NULL,
[EventProcessedUtcTime] [datetime] NULL,
[PartitionId] [nvarchar](400) NULL,
[EventEnqueuedUtcTime] [datetime] NULL,
[IoTHub] [nvarchar](max) NULL,
)

What is the problem? Is it the datatype? Please help me. Thank you!

Answer 1

Azrue SQL Database can convert "2019-05-23T00:19:31.8287610Z" to datetime2 , smalldatetime, time, date, except datetime. Here is the error massage:

According your data , I think the datatype datetime2 best for you.

Please modify your table, alter ActionDate, EventProcessedUtcTime, EventEnqueuedUtcTime columns to datetime2.

Hope this helps.