cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

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?
 
1 ACCEPTED SOLUTION

Accepted Solutions
Dual Super User
Dual Super User

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

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!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

6 REPLIES 6
Highlighted
Dual Super User
Dual Super User

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

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!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Frequent Visitor

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

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)

Highlighted
Dual Super User
Dual Super User

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

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



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Frequent Visitor

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

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

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

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!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Highlighted
Frequent Visitor

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

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.

 

 

Helpful resources

Announcements
firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

Top Solution Authors
Top Kudoed Authors
Users online (9,130)