Answered

Formatting dates - The datetime string must match ISO 8601 format

(0) Share

Report

Posted on by Sam Misemer

Hello all-

I am connecting to a dataverse table and parsing the rows using the ParseJson data operation action. I am outputting this to an SMS (via appending it to a string variable) and when trying to format a specific column that is of Data Type 'Date Only', I am using the formatDateTime('<timestamp>', '<format>'?) function like this:

items('Apply_to_each')[formatDateTime(string('cr16f_paymentdate'),'d')]

and it errors out with the following error:

Unable to process template language expressions in action 'Append_to_string_variable' inputs at line '1' and column '20518': 'In function 'formatDateTime', the value provided for date time string 'cr16f_paymentdate' was not valid. The datetime string must match ISO 8601 format.'.

I get that it's telling me that the timestamp is wrong, but here is the output from the parseJson operation:

"cr16f_paymentdate": "2021-11-04T04:00:00Z",

which as far as I can tell DOES match the ISO 8601 format. My flow works, but I get ugly output which will confuse my poor helpless users.

Can anyone shove me in the right direction on this?

(I also tried this using the convertFromUtc('<timestamp>', '<destinationTimeZone>', '<format>'?) function with the same result)

Thanks in advance for any assistance!!

-SCM

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (1)

Mira Ghaly 11,415 Moderator on at

Like (1)

Report
Copy link

Link copied!

@Lexanar

Can you try removing string from the below expression and use it as below instead?

items('Apply_to_each')[formatDateTime('cr16f_paymentdate','d')]

I tried the same input and it worked but you are looping through an array , have you checked history because this can be caused in case one of the values in the array is null.

Was this reply helpful? Yes No
Verified answer

Sam Misemer 54 on at

Like (2)

Report
Copy link

Link copied!

@Mira_Ghaly -

Thank you for the response! I apologize for taking so long to respond, hectic holidays 🙂 . I changed the code as you requested/suggested to items('Apply_to_each')[formatDateTime('cr16f_paymentdate','d')]
but no change on my side. When pulling from the dataverse table even though the data appears to be in an acceptable ISO 8601 format, the formatDateTime function still threw the value provided for date time string 'cr16f_paymentdate' was not valid. The datetime string must match ISO 8601 format.'. error.

I was able to fix my problem by learning more about the ParseJson function.
I used the List Rows (from Dataverse) action and then made that easier to deal with by using the parse JSON action on the output from the List Rows action. When I looked more closely at ALL of the JSON objects, I found this one :
"cr16f_paymentdate@OData.Community.Display.V1.FormattedValue": "11/4/2021",
which had exactly the format I was looking for, duh. So I solved my problem, but I am still left with the question of why the formatDateTime function doesn't work on the data coming from Dataverse.
I tried inserting static text and the function worked as expected, but anytime the data ORIGINATES from DataVerse whether it comes through the Parse JSON action or even if I access it directly from the outputs of the List Rows action, it throws the value provided for date time string 'cr16f_paymentdate' was not valid. The datetime string must match ISO 8601 format.'. error.
I am going to mark this as solved and closed, but I am still trying to figure out why the formatDateTime function doesn't like DataVerse.

Thanks again for your response!

-SCM

Was this reply helpful? Yes No