You’re offline. This is a read only version of the page.
Hello everyone,
I'm trying to get all Sales Orders with the ConfirmedShippingDate in a specific month.
Since ODATA has a month() function, my first approach of the filter query was:
"month(ConfirmedShippingDate) eq 9"
to filter dynamically for the current month I also thought about
"month(ConfirmedShippingDate) eq month(utcnow())
both of those statements return an error:
"An error has occurred. Property 'Int32 Month' is not defined for type 'Microsoft.Dynamics.Ax.Xpp.AxShared.Date' clientRequestId: 220afd28-0f67-4b1a-9930-f84db1df38d2"
I then came up with this solution, which does work, but is not optimal since it doesn't actually relate to months but only goes back a fixed amount of days
"ConfirmedShippingDate ge @{adddays(utcnow('yyyy-MM-dd'), -31, 'yyyy-MM-dd')} and ConfirmedShippingDate lt @{adddays(utcnow('yyyy-MM-dd'), +0, 'yyyy-MM-dd')}"
To make this more dynamic and usable I tried this to always go back until the actual beginning of the current month:
"ConfirmedShippingDate ge @{adddays(utcnow('yyyy-MM-dd'), -dayofmonth(utcnow()), 'yyyy-MM-dd')} and ConfirmedShippingDate lt @{adddays(utcnow('yyyy-MM-dd'), +0, 'yyyy-MM-dd')}"
I thereupon get the message that the "exppression is not valid"; adding the int()-function doesn't change a thing [-int(dayofmonth(utcnow()))].
Any ideas how to solve this dynamically? It doesn't matter to me if i have to tell the flow which month it is or if it works automatically for the current month. I just want somehting better than going 31 days back when executing the flow at the end of the month (which would cause problems if the month just has 30, 28, or 29 days)
Thank you in advance!
Hi @v-micsh-msft,
looks good! Thank you very much! 🙂
Hi @Christoph_G,
Take a try to modify the formula as below
Share
Report
All responses (
Answers ("ConfirmedShippingDate ge @{Startofmonth(utcnow())} and ConfirmedShippingDate lt @{StartofMonth(adddays(utcnow(), 31))}"
Regards,
Michael
Hi @v-micsh-msft,
I just copied what you suggested into my Flow and get following error:
An error has occurred. The time zone information is missing on the DateTimeOffset value '2017-11-01T00:00:00.0000000'. A DateTimeOffset value must contain the time zone information. clientRequestId: 5f59e51b-f8b0-4574-be81-8c2ed3025a1c
I don't understand why the exact same statement you used causes trouble in my Flow.. any ideas?
Thank you
Christoph
Hello @v-micsh-msft
looks like what I was looking for - thank you for your suggestion. I will try it soon!
Kind Regards,
Christoph
Hi @Christoph_G,
The Month() function will not work under Dynamics 365 OData query, which is documened in the article below:
Quoted:
"
This is a sub-set of the 11.2.5.1.2 Built-in Query Functions. Date, Math, Type, Geo and other string functions aren’t supported in the web API.
"
To filter within the month, we could take a try with the following way:
"ConfirmedShippingDate ge @{Startofmonth(utcnow())} and ConfirmedShippingDate lt @{StartofMonth(adddays(utcnow('yyyy-MM-dd'), 31))}"
Actually we could just take use of the expression under the Filter query:
For the Startofmonth of current month, we use:
#1
David_MA
345
Super User 2025 Season 1
#2
Michael E. Gernaey
271
Super User 2025 Season 1
#3
stampcoin
259
