cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JacobsJ
New Member

Filter EventDate by Date Range

I am trying to create an email reminder flow based on the events in a Sharepoint calendar. 
I am able to successfully create these when using a single expression for EventDate greater than or equal to today. Obviously this then gives me all future events. 

I want to be able to cap this to the Events within the next 45 days. I then Add an EventDate less than or equal to 45 days from today and get an error when testing this flow.

JacobsJ_0-1655726624519.png

 

 

EventDate ge 

formatDateTime(utcNow(),'yyyy-MM-dd')

AND EventDate le

formatDateTime(addDays(utcNow(),45,'yyyy-MM-dd'))

 

This is the error I get:

 

The expression "EventDate ge '2022-06-20' and EventDate le '2022-08-04T00:00:00.0000000" is not valid. Creating query failed.

I can see it showing the Time in the second part which seems odd when the formatDateTime format is 'yyyy-MM-dd'.

Any suggestions

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-yujincui-msft
Community Support
Community Support

Hi @JacobsJ ,

 

Please use and instead of AND, and add single quotes around the expression.

EventDate ge '@{formatDateTime(utcNow(),'yyyy-MM-dd')}' and EventDate le '@{formatDateTime(addDays(utcNow(),45),'yyyy-MM-dd')}'

vyujincuimsft_0-1656298064425.png

vyujincuimsft_1-1656298559110.png

 

 

 

Best Regards,

Charlie Choi

View solution in original post

5 REPLIES 5
David-
Solution Sage
Solution Sage

I don't know if this will work, but try setting adding days to EventDate le using a variable instead. Then in the filter use the variable with formatDateTime([variable name], 'yyyy-MM-dd')). That should strip out the time when you add the days to utc(Now).

v-yujincui-msft
Community Support
Community Support

Hi @JacobsJ ,

 

The result you got is because the second parameter in the formatDateTime() function is empty.

'yyyy-MM-dd' should be enclosed in parentheses in the formatDateTime() function, not in the addDays() function. Please note the position of the brackets.

vyujincuimsft_1-1655879056140.png

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#f...

 

I have made a test for your reference, please try the following expression.

 

formatDateTime(addDays(utcNow(),45),'yyyy-MM-dd')

 

vyujincuimsft_4-1655879626564.png

Result Screenshot:

vyujincuimsft_5-1655879772510.png

 

 
Best Regards,
Charlie Choi

Thanks Charlie.

I didn't even notice that. It did indeed fix the format of the second date. However, it did not fix the error I received. 
So I now have the following filter in place:

 

EventDate ge

formatDateTime(utcNow(),'yyyy-MM-dd')

AND

EventDate le

formatDateTime(addDays(utcNow(),45),'yyyy-MM-dd')

 

I still get this error on the Get Items Step:

JacobsJ_0-1656079323780.png

 

Does anyone know if there is an alternative method for filtering date ranges?

v-yujincui-msft
Community Support
Community Support

Hi @JacobsJ ,

 

Please use and instead of AND, and add single quotes around the expression.

EventDate ge '@{formatDateTime(utcNow(),'yyyy-MM-dd')}' and EventDate le '@{formatDateTime(addDays(utcNow(),45),'yyyy-MM-dd')}'

vyujincuimsft_0-1656298064425.png

vyujincuimsft_1-1656298559110.png

 

 

 

Best Regards,

Charlie Choi

Hi Charlie,

That has worked a treat.
Annoying that is was simple and staring me in the face. Thank you for taking the time to help.

Helpful resources

Announcements
Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (1,791)