From second to dateTime in Azure Stream Analytics

Question

I want to convert 'dd' that is a number that indicate Data device (dd is in second).

At the moment I can extract the Year, Month, Day, Hour with this query in Azure Stream Analytics:

 DATEPART(YEAR, DATEADD(MILLISECOND, dd, '1970-01-01')) AS [Year],
 DATEPART(MONTH, DATEADD(MILLISECOND, dd, '1970-01-01')) AS [Month],
 DATEPART(DAY, DATEADD(MILLISECOND, dd, '1970-01-01')) AS [Day],
 DATEPART(HOUR, DATEADD(MILLISECOND, dd, '1970-01-01')) AS [Hour]

I want to extract the DateTime from dd, so after that i can insert on a database.

Can someone help me?

Ross Bush Ross Bush · Accepted Answer · 2018-03-14T13:42:06

If I understand, you simply need only to calculate the date from the Epoch.

--If dd is milliseconds NOTE : This could cause error as DATEADD does not use BIGINT for conversion

    DECLARE @MyDate DATETIME  = DATEADD(MILLISECOND, dd, '1970-01-01')

--If dd is seconds. The safer route.
    DECLARE @MyDate DATETIME = DATEADD(SECOND, dd, '1970-01-01')

From second to dateTime in Azure Stream Analytics

2 Answers