CODEWITHSUNDEEP
power-automate
MathiasMester
MathiasMester

Reputation: 45

Power Automate - I am trying to format a date, coming from an SQL database

I am trying to create a Flow in Power Automate to retrieve data from an SQL database, and put that data into a Sharepoint calendar.

the Flow is:

  1. Manually Trigger

  2. (SQL) Get Rows (V2)

  3. Compose action formatDateTime(item()?['dato'],'dd-MM-yyyy')

  4. (Sharepoint ) Create Item.

It fails when i try to use the FormatDateTime function in the compose action, with the following error:

InvalidTemplate Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.

Screenshots of the Flow:

Entire Flow

enter image description here

Compose action that fails

enter image description here

Create Item, mapping fields

enter image description here

I hope someoen here is able to help me cause it feels like i'm close

What haven't i tried i've tried to solve this issue in al ot of different ways, i'll try to put some of them down here, as detailed as i can. I am completey new to Power Automate so a lot of the different "solutions" comes from searching.

I have tried to put a another compose action in before the compose action that is supposed to format my date and that one actually works.

Compose action with the "dato" from SQL

enter image description here

The result of the above compose action, it has 13 entries all of them succeeded

enter image description here

The whole issue with trying to format the date, came from the very beginning where i would just grab and map the "Dato" field from the SQL database directly in the (Sharepoint)Create Item.

But then i would get this error message on only 2 of the dates from "Dato" So i thought i wouls just format all the date from "dato" just in case

I do get the first 4 entries into my sharepoint calendar but then i fails on the

OpenApiOperationParameterTypeConversionFailed The 'inputs.parameters' of workflow operation 'create_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/StartDato' is required to be of type 'String/date'. The runtime value "17-01-2024" to be converted doesn't have the expected format 'String/date'.

If you want the outputs please tell, an i will post them but it's a lot of text.

Other than that i have tried different ways with "Initialize variable" and "compose" actions without luck

UPDATE:

It's always the same 2 dates 17-01-2024' and '16-01-2024' it's possible that it's really just those 2 dates i have to fix, they look normal in the database but might have gotten "corrupted" or something.

As for the rest of the dates they do get imported into the calendar. But i am from Denmark working in IT on a school, and the dates i am trying to get into a sharepoint calendar comes from a database, that gets populated with data everytime a user fills out a form, where they request IT help for an event.

So the dates are coming in with the format dd-MM-yyyy and for all the dates with a "dd" under 12 they do go into the calendar but "dd" and "MM" are switched around. So 09-01-2024 becomes 01-09-2024 i want the first format.

so i need to find a way to keep the format as dd-MM-yyyy

screenshot of the dates("dato") from the database screenshot from the sharepoint calendar(list)

Upvotes: 1

Views: 10160

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

The original issue is that SharePoint doesn't recognize the date format dd-MM-yyyy.

Strongly suggest you use the ISO format of yyyy-MM-dd for passing dates to SharePoint (or most other connectors).

Update your flow back to the original and add the following to your create item:

formatDateTime(parseDateTime('14-01-2024', 'da', 'dd-MM-yyyy'), 'yyyy-MM-dd')

Replace '14-01-2024' with the relevant expression from your DB date field.

Upvotes: 4

Related Questions