How can I lead pad zeros in a Stream Analytics Query?

Question

Within a Stream Analytics query, I'm trying to convert a date to a simple ISO 8601 date-only format, like so:

CONCAT(
    c.IoTHub.ConnectionDeviceId, 
    '@', 
    CAST(DATEPART(year, EventProcessedUtcTime) as nvarchar(max)), 
    '-',
    CAST(DATEPART(month, EventProcessedUtcTime) as nvarchar(max)), 
    '-',
    CAST(DATEPART(day, EventProcessedUtcTime) as nvarchar(max))
) as partitionkey

This works alright, but it produces dates like so:

2017-3-5

I'd prefer that the dates be lead-zero-padded, like this:

2017-03-05

This is a simple thing in T-Sql, but I don't see any of the tools necessary to make this or any of the other tricks work to be present in the SA query language subset.

Anybody know a way to tackle this?

Answer 1

According to your requirement, I assume that you could use Azure Stream Analytics JavaScript user-defined functions.

For example, I just created a user-defined function named dateFormat as follows:

function main(dateStr) {
    var date=new Date(dateStr);                                  
    var mm = (date.getMonth()+1).toString();        
    var dd  = date.getDate().toString(); 
    return date.getFullYear().toString() + '-' + (mm[1]?mm:"0"+mm[0]) + '-' + (dd[1]?dd:"0"+dd[0]);
}

Usage:

SELECT UDF.dateFormat(EventProcessedUtcTime) FROM [YourInputAlias]