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

[Anonymous]

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?

[v-yamao-msft]

Hi @Anonymous,

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:

 

Best regards,

Mabel Mao

[Anonymous]

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?

[Anonymous]

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:

and here's the Get Items query:

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

[v-yamao-msft · Accepted solution]

Hi @Anonymous,

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.

 

Best regards,

Mabel Mao

[Anonymous]

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.