cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kudo Kingpin
Kudo Kingpin

ODATA Filter for SharePoint Date/Time field issues

We have a SharePoint list of events which includes a column named "StartDateTime". We want to use Flow to produce a list (and eventually a table) of events where StartDateTime is more than 1 day in the future. Seems like it should be pretty simple, but while the Flow Checker says our expression is OK, the Flow fails claiming that the filter is invalid.

First, to get the "today plus one" timestamp, I added an "Get future time" action. As shown below, this is working fine and produces the timestamp in what should be an appropriate format (2019-03-26T16:37:37.7896525Z).
get future time output
Next, I use "Get items" and add a filter query that should work:
get items
When we run the Flow, the error message is: 

The expression "StartDateTime gt 2019-03-26T16:46:59.2064641Z" is not valid.
clientRequestId: 76f3d053-12f5-4139-8e5f-cf5344bdb0c7
serviceRequestId: c57ecc9e-b070-8000-5893-d38bda5acc26


If we remove the filter, the Flow runs, but (obviously) gets all of the items from the list. In the output of that, I can see that the StartDateTime field is being read in a slightly different (shorter) format:

StartDateTime": "2019-04-02T16:00:00Z


So, I replace the "Future time" dynamic data with this expression (to be sure it's in the same format as the SharePoint date/time field value):

        formatDateTime(body('Get_future_time'),'yyyy-MM-ddThh:mm:ssZ')

We STILL get an error:
       The expression \"StartDateTime gt 2019-03-26T04:56:38Z\" is not valid.\r\nclientRequestId: 70b6073c-111f-4fc1-bc0d-
       e101fa134954\r\nserviceRequestId: 527fcc9e-c0db-8000-56e3-917f5330103e

So, can anyone help to clarify what we're doing wrong here? We're running out of ideas.
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: ODATA Filter for SharePoint Date/Time field issues

@ChadVKealey :

 

I believe that your issue is that you need to put single quotes around Future Time:

 

SingleQuote.jpg

 

If this addresses your issue, please mark your post as Solved.

If you like my response, please give it a Thumbs Up.

 

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

Scott

View solution in original post

2 REPLIES 2
Highlighted
Super User III
Super User III

Re: ODATA Filter for SharePoint Date/Time field issues

@ChadVKealey :

 

I believe that your issue is that you need to put single quotes around Future Time:

 

SingleQuote.jpg

 

If this addresses your issue, please mark your post as Solved.

If you like my response, please give it a Thumbs Up.

 

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

Scott

View solution in original post

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: ODATA Filter for SharePoint Date/Time field issues

Thanks, @ScottShearer, that did the trick. I'm finding that there seems to be a lot of inconsistency with the syntax used for different types of expressions and values in Flow. I wish there was an authoritative and comprehensive reference, or that Flow would do a better job of identifying those errors before letting you save.

Helpful resources

Announcements
FirstImage

Microsoft Ignite 2020

Check out the announcement of Power Platform content at Microsoft Ignite!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (1,807)