filter 'list items present in table' on month and day

[Rbie]

Hi all,

I'm trying to accomplish something that seems trivial but I can't get it to work.

I have a 'List items present in table' which gets records from the worker table in dynamics F&O

I want to filter this data with the 'Filter query' to filter the people who's birthday it is today. 

I thought it was as simple as 
month(BirthDate) eq @{int(formatDateTime(utcNow(),'mm'))} and day(BirthDate) eq @{int(formatDateTime(utcNow(),'dd'))}
But it seems those datetime functions aren't supported in the F&O API.

I know I can get all the records and filter them afterwards with a condition but the worker table has 50.000+ records.

So I don't want to go that route if I can help it.

Thanks in advance,

Rbie

[grantjenkins]

You can try the following which checks if the date is between the start of today (inclusive) and the start of tomorrow (exclusive). This is assuming the internal name of your column is BirthDate.

BirthDate ge '@{startOfDay(utcNow())}' and BirthDate lt '@{startOfDay(addDays(utcNow(), 1))}' 

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[Rbie]

@grantjenkins ,

Thanks for your reply , but this doesn't give me the people who's birthday it is today,

This

BirthDate ge '@{startOfDay(utcNow())}' and BirthDate lt '@{startOfDay(addDays(utcNow(), 1))}' 

actually this shouldn't give any records since the above will never be true, unless that person is born today

[grantjenkins]

I can't believe I got that so wrong - sorry about that. I'm just off to sleep (1AM for me) but will try to get something workable when I wake up.

Can you show the actual date values you are working with. Are they in the format yyyy-MM-dd (example: 2002-08-23), or something else?

[Rbie]

Hi Grantjeskins,

Both BirthDate and utcNow are in this format '1900-01-01T12:00:00Z'

[grantjenkins · Accepted solution]

I thought this would end up being fairly easy to achieve, but alas, not to be.

Initially I thought I could just use contains with in the Filter Query but seems as though it brings back the dates as a serial number behind the scenes, checks the condition on that, then brings back the date is ISO date format.

I thought the following expression would work that checks just the month and day part of the date string. However, it didn't work. The contains does work for other fields - just not the date.

contains(BirthDate, '@{formatDateTime(utcNow(), '-MM-dd')}')

The other two options I can think of are to utilize Office Scripts where you can pass in the Excel file, and it will return a JSON array, but within the script filter the dates as we want. I haven't worked with dates in Office Scripts before but might see what's possible.

The other option is to add an additional column (if possible) to your Excel Table with the following formula which will get the month and day from the BirthDate and we can use that within the Filter Query.

The Excel formula I used is below:

//The TEXT function ensures the leading zero isn't truncated for single digits

=TEXT(MONTH([@BirthDate]), "#00") & "-" & TEXT(DAY([@BirthDate]), "#00")

And the Excel file:

And in the Filter Query in the flow:

BirthDay eq '@{formatDateTime(utcNow(), 'MM-dd')}'

[Rbie]

Unfortunately I'm not working with an Excel file but with a Dynamics 365 F&O table where I can't add columns to.

So it seems I will have to filter the data afterwards. Strange that something so trivial isn't possible.

Anyway, thanks for your time.