How can we improve Microsoft Azure Stream Analytics?

Add a simple Datetime format function

I need to insert the timestamp as a column in a Azure Table with the following .NET format ("yyyy-MM-dd-HH-mm"), I thought the CONCAT and DATENAME/DATEPART functions would help me, and I ended up with this, but it is not producing the output I need:

CONCAT(DATENAME(yyyy,System.Timestamp),'-',CAST(DATEPART(mm,System.Timestamp) AS NVARCHAR(MAX)),'-',DATENAME(dd,System.Timestamp),'-',DATENAME(HH,System.Timestamp),'-',DATENAME(mi,System.Timestamp)),

"2015-10-12T05:17:37.807Z" is formatted like: '2015-10-12-5-17" and I am expecting "2015-10-12-17-17"

45 votes
Vote
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
You have left! (?) (thinking…)
Anonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

4 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • Mart commented  ·   ·  Flag as inappropriate

    This is old, but I too had a similar struggle.
    CAST(CAST(event.TimeStamp as datetime) as nvarchar(MAX))

    I used this to with concat to create a row key, it gives the output:
    2017-05-25T18:53:06.6638136Z

    instead of a "2017/05/25 06:53:06 PM"

    might save someone 5 mins

  • Peter Johnson commented  ·   ·  Flag as inappropriate

    DateTimeFromParts creates a datetime object, a benefit of a string output is ability to concat with another string for use as a partition or row key.

  • Christos Karras commented  ·   ·  Flag as inappropriate

    DateTimeFromParts converts string to a DateTime. I think this is asking for the reverse operation (convert a DateTime to a string with a specific format).

    Currently I have this solution to format a DateTime, with handling addition of leading 0 to have 2 digits:

    CAST(DATEPART(yyyy,EventEnqueuedUtcTime) AS NVARCHAR(MAX)),
    CASE WHEN DATEPART(mm,EventEnqueuedUtcTime) < 10 THEN CONCAT('0',CAST(DATEPART(mm,EventEnqueuedUtcTime) AS NVARCHAR(MAX))) ELSE CAST(DATEPART(mm,EventEnqueuedUtcTime) AS NVARCHAR(MAX)) END,
    CASE WHEN DATEPART(dd,EventEnqueuedUtcTime) < 10 THEN CONCAT('0',CAST(DATEPART(dd,EventEnqueuedUtcTime) AS NVARCHAR(MAX))) ELSE CAST(DATEPART(dd,EventEnqueuedUtcTime) AS NVARCHAR(MAX)) END,
    CASE WHEN DATEPART(hh,EventEnqueuedUtcTime) < 10 THEN CONCAT('0',CAST(DATEPART(hh,EventEnqueuedUtcTime) AS NVARCHAR(MAX))) ELSE CAST(DATEPART(hh,EventEnqueuedUtcTime) AS NVARCHAR(MAX)) END

    This is much too complex and could be simplified with a date formatting function.

Feedback and Knowledge Base