Azure Stream Analytics - Split date and time

Question

I am trying to split up the date and time components for a ASA query and am having some difficulties. I did try and use the concat, datepart and dateadd functions to achieve this but it comes up with a Query compilation failed error. Any ideas on what I am doing wrong or another better way of achieving this?

ConCat(DatePart(hh,DateAdd(Hour,11,System.Timestamp)),':',DatePart(mm,DateAdd(Hour,11,System.Timestamp))) as Time,

Thanks

Answer 1

Concat function takes string as input, you will have to cast the output of datepart() to string. Below should work.

select 

concat(
    cast( datepart(yy,System.Timestamp) as nvarchar(max)),
    '-',
    cast( datepart(mm,System.Timestamp) as nvarchar(max)),
    '-',
    cast( datepart(dd,System.Timestamp) as nvarchar(max))) [Date],

concat(
    cast( datepart(hh,System.Timestamp) as nvarchar(max)),
    ':',
    cast( datepart(ss,System.Timestamp) as nvarchar(max))) [Time]

into outputStore from inputSource