cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ChadVKealey
Level 10

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
Super User
Super User

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
Super User
Super User

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

ChadVKealey
Level 10

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (7,851)