IoT Hub / Stream Analytics - SQL - Convert incoming timestamp to Date Time

Question

I have a timestamp coming in as milliseconds after 01/01/1970 ([timestamp] below is the column name for the data), and need to add an additional column that uses SQL to take these milliseconds and convert it to a date/time format.

I have attempted:

SELECT DATEADD(MILLISECOND, [timestamp] %1000, DATEADD(SECOND, [timestamp] /1000, '19700101'))

I am getting the error:

Cannot cast value '19700101' of type 'nvarchar(max)' to type 'datetime' in expression 'DATEADD ( SECOND , [timestamp] / 1000 , '19700101' )'. At line '2' and column '152'.

Any help is appreciated, thank you! - M

Answer 1

You need to specify 19700101 in ISO timestamp format instead, so 1970-01-01T00:00:00Z

SELECT DATEADD(MILLISECOND, [timestamp] %1000, DATEADD(SECOND, [timestamp] /1000, '1970-01-01T00:00:00Z'))