4

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.

Community
  • 1
  • 1
ReignOfComputer
  • 747
  • 2
  • 10
  • 30

1 Answers1

3

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'
Robert McKee
  • 21,305
  • 1
  • 43
  • 57
  • That did it, thanks! My final code is: `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',` as `CAST(0 AS DateTime)` does not work on Azure T-SQL. – ReignOfComputer Jul 13 '15 at 15:28
  • Updated answer with final solution. – Robert McKee Jul 13 '15 at 15:33