T-SQL Azure Stream Analytics Rounding to Minute

Question

I have the following SELECT clause in my Azure Stream Analytics Query:

SELECT DateAdd(mi, DateDiff(mi, 0, DateAdd(s, 30, Max(timecreated))), 0) as 'timestamp'

Which is giving the following error:

Second parameter of 'DateDiff' in expression 'DateDiff ( mi , 0 , DateAdd(s, 30, Max ( timecreated ) ) )' has invalid type 'bigint'. 'datetime' is expected.

Admittedly, the code I'm using is copied from several similar threads on StackOverflow, such as T-SQL datetime rounded to nearest minute and nearest hours with using functions, but I've no idea what to change the 0 to in my scenario.

Answer 1

It should have auto cast the 0 from bigint to datetime, but there may be some quirks with the Azure version of T-SQL. Instead, use:

SELECT DATEADD(mi, 
  DATEDIFF(mi, CAST('1900-01-01 00:00:00.000' AS DateTime), 
    DATEADD(s, 30, MAX(timecreated))),
  CAST('1900-01-01 00:00:00.000' AS DateTime)) as 'timestamp'