Azure Data factory - How to convert data time to some specific format in pipeline expression builder?

Question

Input: "firstRow": { "flag": false, "WindowStart": "2022-12-23:18:00:000Z"

I'm using the below format, formatDateTime(activity('Lookup1').output.firstRow.windowStart,'dd-MM-yyyy hh:mm')

but its throwing an error as

Operation on target Copy_staging_to_raw failed: In function 'formatDateTime', the value provided for date time string '2022-12-23:18:00:000Z' was not valid. The datetime string must match ISO 8601 format

Could you please help me what was the mistake on this?

Answer 1

Operation on target Copy_staging_to_raw failed: In function 'formatDateTime', the value provided for date time string '2022-12-23:18:00:000Z' was not valid. The datetime string must match ISO 8601 format

The error message shows your provided date time string '2022-12-23:18:00:000Z'is not valid for ISO 8601 format.

the ISO 8601 timestamp normally looks like this:

String dateString = "2019-09-26T07:58:30.996+0200"

This string is now structured in the date format YYYY-MM-DD. The delimiter for the time is “T” and the time format is hh:mm:ss plus the UTC suffix .sssz. The complete format is therefore: YYY-MM-DD “T” hh:mm:ss.SSSZ.

You could use this converter tool for the convert.

Answer 2

You can use the following dynamic content instead, to get the desired result.

• The following is the sample of lookup output same as yours:

Using string and collection functions, I have converted the lookup output data to ISO 8601 format string and converted it to dd-MM-yyyy hh:mm format:

@formatDateTime(concat(first(array(split(activity('Lookup1').output.firstRow.windowStart,':'))),'T',join(take(skip(array(split(activity('Lookup1').output.firstRow.windowStart,':')),1),2),':')),'dd-MM-yyyy hh:mm')