Getting just the Month number from a Date field in filter query

(0) Share

Report

Posted on by CU25091147-0

I have a Date field in my list and just want to extract the Month number.

Essentially i would like the query to be something like:

formatDateTime('Date','MM') eq '03'.

In order to get only the values from March from the list.

The above expression does not work.

How can i achieve this?

Categories:

Building flows

I have the same question (1)

All responses (6)

Answers (1)

efialttes 14,756 on at

Like (0)

Report

Hi!

Sharepoint list?

If so, are getting the info via 'Get item' or via trigger 'When a item is created'?

Or, did you get it by means of 'Get items', and you wanna get the trick inside an 'Apply to each' / 'Select' / 'Filter array'?

Thanks!

Was this reply helpful? Yes No
CU25091147-0 34 on at

Like (0)

Report

My trigger is from a Powerapp. But I am using Get Items to get items from the Sharepoint list based on the filter query to then Apply to Each from the result. The filter query is based based on the Date from the Sharepoint list and the month number eq value is fed from the Powerapp. For an example, I am getting the month value in Powerapps using Month(StartDate)

Was this reply helpful? Yes No
efialttes 14,756 on at

Like (0)

Report
Hi!

I think the problem is, when using oDATA expression in your Filter Query input, you need the following sintax:

ColumnName operator value

...but you cannot manipulate your column name by using

expression(Columname) operator value

The approach you have designed can work if you replace Filter Query and use a 'Filter array' action block just after 'Get items' instead, just need to assign 'Get items' output as 'Filter array' input.

Does this new approach make sense to you?

Please let me know and we will start working on it

Was this reply helpful? Yes No
CU25091147-0 34 on at

Like (0)

Report

Okay. Its seems strange that you cant essentially substring a column before a compare.

I have added a Filter array with:

{
    "inputs": {
        "from": "@body('Get_items')?['value']",
        "where": "@equals(formatDateTime(item()?['Date'], 'MM'), 03)"
    }
}

Or adnaced. @equals(formatDateTime(item()?['Date'], 'MM'), 03)

So now i need to Apply to each on the body of this?

Thanks.

Was this reply helpful? Yes No
Verified answer

efialttes 14,756 on at

Like (0)

Report
Hi!

No need for an 'apply to each' yet.

Please note the following

equals(formatDateTime(item()?['Date'], 'MM'), '03')

formatDateTime() output is a string, so you need to wrap 03 with single quotes

Can you run manually your flow, and inspect Filter array outputs? You will probably get an array of items matching your requirements? Hope there are just a few, if not we can add a 'SElect' action block just after the 'Filer array' for troubleshooting purposes

Thanx!

1 people found this reply helpful.

Was this reply helpful? Yes No
CU25091147-0 34 on at

Like (1)

Report

Thanks, that has worked. Apply to Each on the body of Filter array is working correctly as well and producing the output i am expecting. I think this is solved.

Thank you for your help.

Was this reply helpful? Yes No