CODEWITHSUNDEEP
azure-stream-analytics
BrentA
BrentA

Reputation: 172

Azure Stream Analytics - Split date and time

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

Upvotes: 1

Views: 1213

Answers (2)

Steffen Mangold
Steffen Mangold

Reputation: 1144

You have two possible solutions for this:

1. Use T-SQL like syntax

DATEADD(dd, DATEDIFF(dd, '0001-01-01 00:00:00', [TimeStamp]), '0001-01-01 00:00:00')

2. Use build in DateTime builder

DATETIMEFROMPARTS(DATEPART(year,[TimeStamp]),DATEPART(month,[TimeStamp]),DATEPART(day,[TimeStamp]),00,00,00,00)

Upvotes: 1

Vignesh Chandramohan
Vignesh Chandramohan

Reputation: 1306

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

Upvotes: 2

Related Questions