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

have to tried to specify 19700101 in ISO timestamp format instead? so "1970-01-01T00:00:00Z"? — silent, Dec 03 '20 at 11:01
@silent what's the timestamp data look like, can you show me one? — Leon Yue, Dec 04 '20 at 01:49
@leon Yue The timestamp data format coming in looks like: 1607040000021. A quick google search shows that it may be called "UNIX Epoch"? — user14750197, Dec 05 '20 at 02:44
@Silent I have not tried that yet, unfortunately I am not able to change the data format before it lands in IoT Hub, only able change it by querying it through Stream Analytics. — user14750197, Dec 05 '20 at 02:47
I’m not talking about your input data. But about the very last part in your query — silent, Dec 05 '20 at 13:09
glad to hear it. I made an answer out of that. please accept that if it works — silent, Dec 07 '20 at 13:49

score 0 · Answer 1 · answered Dec 07 '20 at 13:49

0

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'))

answered Dec 07 '20 at 13:49

silent

14,494
4
46
86

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

1 Answers1