Get items and filter query by date contains day and month

[EduardoMC]

Hello community,

Bieleve did enough research but still not able to solve what I'm looking for. I'm trying to create a flow which sends employees congratulations when it's their birthday or anniversary in the company. I'm able to create the flow by the traditional (believe not the optimal) way of "Get items" from a SP list, then filter the array and then send the email.

How could I apply properly "Filter query" OData instructions by specifying if the date column contains the current day and month?

Tried:

• A custom column in SharePoint that specify if the dd-mm is the same as today, tried to Get items/Filter query by such column but seems that Power Automate still not supports custom columns.
• Followed "Use OData query operations in SharePoint REST requests". According to supported query options I can filter by dates. Tried different syntax instructions, nothing works.
• Followed some examples here in the community but nothing specific about day and month.
• I can filter by specific dates as Birthday eq '1993-07-04'. Nothing comes when:
  • substringof('1980',Birthay)
  • Birthday contains '1980'
  • contains(Birthday,'1980') 

Thank you, really appreciate your support.

Best.

[v-litu-msft]

Hi @EduardoMC,

Get items action not support to use contains Odata filter query, but you could use Get items without any filter query to get all items, then add a Filter array under it to filter out items that Birthday column contains string "1980":

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EduardoMC]

Thanks @v-litu-msft for your advice. It may not apply for my personal case. The SharePoint list will have around 17K registers (employees) and I believe the max of get items is 5K as results. Turned to SharePoint API due its better performance but anyway I created a static column for days and months. Even that the OData specify dates filters format it seems that doesn't work with SharePoint Online.

If you have any suggestion is more than welcome.

Thank you, best!

[fchopo]

Hello @EduardoMC 

Could you create a calculated column in SharePoint with the day (DayBirthday) and another one with the month (MonthBirthday) of the employee birthday? In this way you could use an ODATAFilter query like:

DayBirthday eq int(utcNow('dd')) and MonthBirthday eq int(utcNow('MM'))

To get the current day: int(utcNow('dd'))

To get the current month: int(utcNow('MM'))

Hope it helps!

Ferran

[Paulie78]

@EduardoMC I too wanted to figure out how to do this in the way that you have explained. I have done two videos on it, one which demonstrates how to build the Happy Birthday greeting, and another which explains how to filter on the Day and Month values of a date field only:

How to send automate birthday or anniversary emails with Power Automate 

How to filter a SharePoint Date column on Day, Month or Year 

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘