cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NPBA_AT
Level 8

Comparing Sharepoint DateTime value to converted DateTime values to filter records in Get Items

I am trying to create a filter expression in a Sharepoint Get Items where I want to filter on a trasaction date within a date range. I have tried many variants and cannot seem to produce an expression that is valid. When I create a "Get Items" that filters on different criteria I can see that my TransactionDateTime looks like this inside the Flow:

"TransactionDateTime": "2018-09-18T23:21:00Z",

 

I am passing two parameters to the Flow for the from and to dates for the range. I cannot find a way to format these dates that is a valid comparison to the Transaction date and I can't work out why. The Sharepoint date time format does not seem to exactly match any of the standard date formats. 

When I try this expression:

TransactionDateTime gt convertToUtc(triggerBody()?['Parameter1'],'New Zealand Standard Time','s')

I get this error:

"status"400,
  "message""The expression \"TransactionDateTime gt 2018-09-16T12:00:00\" is not valid.

 

When I try this expression:

TransactionDateTime gt convertToUtc(triggerBody()?['Parameter1'],'New Zealand Standard Time','u')

I get this error:

 "status"400,
  "message""The expression \"TransactionDateTime gt 2018-09-16 12:00:00Z\" is not valid.

 

I can't seem to use Flow expressions to transform TransactionDateTime to another format.

 

Can anyone give me some advice on how to make this filter query work?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Comparing Sharepoint DateTime value to converted DateTime values to filter records in Get Items

Hi @NPBA_AT,

 

What’s the format of column TransactionDateTime?

From the screenshot, the DateParameter1 is of format “2018-09-17T12:00:00Z”. Please make sure the two dates do have the same format.

Besides, in the Filter query field, please surround the dynamic content with a single quote.

 1.PNG2.PNG3.PNG

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Community Support Team
Community Support Team

Re: Comparing Sharepoint DateTime value to converted DateTime values to filter records in Get Items

Hi @NPBA_AT,

 

What’s the format of Parameter1? Is it date and time format?

 

The format of TransactionDateTime is “2018-09-18T23:21:00Z”, then you need to format another date to the same format as “yyyy-MM-ddThh:mm:ssZ”.

 

I have made the following test, please take it for a reference. In the flow, I will compare the Created date with Date1 (date and time format) column in a SharePoint list.

 

As Created is of format “yyyy-MM-ddThh:mm:ssZ”, then I will have to format the Date1 to the same using the following code:

formatDateTime(triggerBody()?['OData__x006a_c67'],'yyyy-MM-ddThh:mm:ssZ')

Note: Please make sure surround the code with a single quote.

 

Images for your reference:

1.PNG2.PNG 

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
NPBA_AT
Level 8

Re: Comparing Sharepoint DateTime value to converted DateTime values to filter records in Get Items

Hi @v-yamao-msft,

TransactionDateTime is a Sharepoint DateTime type column. The dates being read from another Sharepoint list are a single line of text format. They are being reformatted to a comparable date format, apparently successfully, but don't seem to be able to be compared. 

 

I used the format that you specified in a convert to UTC expression, as TransactionDateTime is in UTC format, and in the error message the date appears to be the same format as the TransactionDateTime, but it still fails.

My filter expression now reads:

TransactionDateTime gt convertToUtc(triggerBody()?['Parameter1'],'New Zealand Standard Time','yyyy-MM-ddThh:mm:ssZ')

and it fails with:

{
  "status"400,
  "message""The expression \"TransactionDateTime gt 2018-09-16T12:00:00Z\" is not valid.\

 

I note that in your example you appear to be comparing two dates that are not part of the data set that you are filtering on - is that right? My problem appears to be that I cannot get the date in the dataset that I am trying to filter to compare with a date that I can format inside the Flow. You appear to have formatted both dates inside the Flow whereas I cannot work out how I can format TransactionDateTime so that it will compare with a date formatted inside the Flow.  In my example the parameter dates are in the record that is being being created and triggering the Flow, and the TransactionDateTime is in a different dataset that I wish to filter by these dates.

 

Can you please try with an example where you compare against a date in the dataset you are filtering on?

NPBA_AT
Level 8

Re: Comparing Sharepoint DateTime value to converted DateTime values to filter records in Get Items

Hi @v-yamao-msft,

Thanks for looking at my problem. I would greatly appreciate any assistance as I've been trying to make this work for days.

 

I did a further test where I have changed the format of the parameter columns in the triggering Sharepoint list to be DateTime format.  I am now doing a simple comparison of the dates which are both Sharepoint DateTime types without any other formating in Flow and this still fails in the same way when I run the Flow. 

Here's the parameter data in the Trigger output:

Capture.PNG

and here's the Get Items query:

Capture1.PNG

 

And here is how it fails again when I run the Flow Smiley Sad

Capture2.PNG

Community Support Team
Community Support Team

Re: Comparing Sharepoint DateTime value to converted DateTime values to filter records in Get Items

Hi @NPBA_AT,

 

What’s the format of column TransactionDateTime?

From the screenshot, the DateParameter1 is of format “2018-09-17T12:00:00Z”. Please make sure the two dates do have the same format.

Besides, in the Filter query field, please surround the dynamic content with a single quote.

 1.PNG2.PNG3.PNG

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
NPBA_AT
Level 8

Re: Comparing Sharepoint DateTime value to converted DateTime values to filter records in Get Items

Thank you Mabel. Its the quotes that have made the difference. It seems very unusual to need to put quotes around a variable name, or around a function that says that it returns a string value.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

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

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 Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 500 members 4,465 guests
Please welcome our newest community members: