Unanswered

Troubles with the "Get items" node and a formatDateTime Conversion after changing the regional settings of the SharePoint list

(0) Share

Report

Posted on by Annannas

Dear community,

I have some troubles with my flow. I tried to use the Get items node with the filter query : Column X eq formatDateTime(addDays(utcNow(),5), 'yyyy-MM-dd').

I found out that the node always gave an empty output because my SharePoint list had a German region and thus, it didn't recognise the dates in Column X. I changed the region to the USA. However, I face the problem now, that the entries in column x are still not correctly recognised by the flow. The "Get Items" node still throws an error. The node works as soon as I manually change the date for the rows. I suspect that the dates in column X are still formatted in the wrong date format for the flow. Do you know how to change the dates in column X so that they are recognized by the flow and I don't have to manually change all the dates?
The list has about 1,000 entries.

All responses (3)

Answers (0)

Ellis Karim 11,105 Super User 2025 Season 1 on at

Like (0)

Report

Re: Troubles with the "Get items" node and a formatDateTime Conversion after changing the regional settings of the SharePoint list

[CORRECTION] Apologies, the paragraph should read:

Another example: Created le '2021-11-23' will return records with a date less than or equal to 2021-11-23 where Hour=00 (midnight) Minutes=00 Seconds=00. The filter query will not include records created after 2021-11-23 Hour=00 (midnight) Minutes=00 Seconds=00. Therefore an item created at 10:15 AM on 2021-11-23 will not be included in the query even though it was created on 2021-11-23.

Therefore if you want include all records created on or before '2021-11-23' just add one day and use the less than operator: Created lt '2021-11-24' (lt = less than).

Hope this clarifies the paragraph.

Ellis
Annanas 2 on at

Like (0)

Report

Re: Troubles with the "Get items" node and a formatDateTime Conversion after changing the regional settings of the SharePoint list

Hi @ekarim2020,

thank you so much for your answer. Column X is a date column. The problem I have is that it appears that the date displayed has been changed correctly in the view (from dd-mm-yyyy to mm-dd-yyyy), but not when the flow matches the data. The flow only matches the data correctly if I have changed the date once and set it back to the original value. Therefore, it leads me to believe that the date was only changed correctly in the interface.

Created ge '2021-11-23' means a date greater or equal to 2021-11-23 where Hour=00 Minutes=00 Seconds=00
The filter query will not include records created after 2021-11-23 Hour=00 Minutes=00 Seconds=00. Therefore an item created at 10:15 AM on 2021-11023 will not be included in the query even though it was created on 2021-11-23.
Do you know a workaround for this problem? I didn't know that so far.
Ellis Karim 11,105 Super User 2025 Season 1 on at

Like (0)

Report

Re: Troubles with the "Get items" node and a formatDateTime Conversion after changing the regional settings of the SharePoint list

Hi @Annannas ,

(1) The Filter query will require the date expression to be enclosed in single quotes

(2) The date format for the filter query will be yyyy-MM-dd

[CORRECTION] Another example: Created le '2021-11-23'

means a date less than or equal to 2021-11-23 where Hour=00 Minutes=00 Seconds=00

The filter query will not include records created after 2021-11-23 Hour=00 Minutes=00 Seconds=00. Therefore an item created at 10:15 AM on 2021-11023 will not be included in the query even though it was created on 2021-11-23.

(3) To read more than a 100 items in a Get items action you can adjust the pagination settings:

To read more than 5000 records, see Get more than 5000 items from SharePoint with Power Automate (tachytelic.net)

Is Column X a SharePoint field of type Date?

Can you post some sample data please?

Hope this helps.

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.