Compare one date field (format: dd/mm/yyyy) with another date field (format: dd/mm/yyyy hh:mm) from two separate Sharepoint-lists with the flow ODATA filter

[Leah_]

Hi all, 

Context: I have two Sharepoint lists A and B. My goal is to check if the B-list items are in the A-list, and mark those items as duplicates with a boolean.

Issue: 

The column in list B is a Date field called B-Date which contains only information of type "dd/mm/yyyy"

The column in list A is a Date field called A-Date which contains information of type "dd/mm/yyyy" or "dd/mm/yyyy hh:mm"

So, when my ODATA Query is the following:

A-Date eq 'B-Date' 

when A-Date contains Time information as well, there will be no duplicate match (as "01.01.2001 12:00" is different from "01.01.2001")

(In the SharePoint view itself it is only showing the date, but I figured the issue out when exporting the data to excel: 

)

Solution I am looking for: So I want to ignore the Time Stamp for the ODATA filter.

I tried creating another calculated-column in the Sharepoint list with =TEXT(A-Date, "dd/mm/yyyy"). But when trying to use this in ODATA filter, I get the error message saying that I can't use a calculated column. 

I also tried to write my filter with a compose-expression first, but I couldn't figure that out. 

Any help / idea is appreciated.

[ScottShearer · Accepted solution]

@Leah_ 

Here is a link to a blog post that describes how to compare two SharePoint lists.

The blog posts describes a different approach than you are using.

In your situation, you want to compare two date columns.  If you follow the example in the link provided above, use the following expression to format the A-Date before comparing:

formatDateTime(A-date ref from Dynamic properties, 'yyyy-MM-dd')

SharePoint stores all dates internally in the yyyy-MM-dd format.  Using the expression that I have provided will strip out the time portion.

[Leah_]

Hi @ScottShearer 

Thank you for your response!

I don't know why I did it so complicated. Thanks to your input I figured it out some way. 

Here is what I did: 

- Get items of list B

- Initialize Variable varADate as String empty

- For each value of list B

- Get items of list A (I do some OData filtering here already)

- Set Variable varADate with formatDateTime(A-date, 'yyyy-MM-dd')

- Filter array where varADate equals B-Date

- If length(Body('Filter_array')) greater or equal to 1, then update item accordingly.

I know that from a performance point of view this is not the best way to do it, as I am querying the items of list A for every item of list B. I wasn't able to figure out how to query the list once and filter afterwards. But I am happy for now with my flow. 

Kind regards
Leah