I don't understand how to filter on datetime columns in MS Flow / Power Automate. This should be one of the easiest filters to apply...
This page includes an example with pictures, but when replicated does not work:
WorkOrderCreatedTime gt addDays(utcnow('yyyy-MM-ddTHH:mm:ssZ'),-30)
Spam filter I think is triggering on my links sorry for the funky format:
https://www.odata.org/documentation/odata-version-2-0/uri-conventions/
Note: One of the comments states that it should be surrounded in single quotes, but that also does not appear to work
This page states that the filter criteria for a date/datetime should be surrounded in single quotes and preceded by the word datetime, but this does not appear to work.
DateTime values must be delimited by single quotation marks and preceded by the word datetime, such as datetime'2010-01-25T02:13:40.1374695Z'.
Spam filter I think is triggering on my links sorry for the funky format:
https://docs.microsoft.com/en-us/previous-versions/dynamicsnav-2016/hh169248(v=nav.90)
Doing year(X) and month(X) etc is not an acceptable solution as it will not work if querying data older than 1 calendar year.
Here are screen shots of things I've tried that don't work (StormDate is my datetime column). Successful response is included at the bottom to demonstrate the format of the response when I leave this particular filter off.
They pretty much all end like this:
Here's a successful response where I leave the filter off:
Solved! Go to Solution.
Hi @Ajh
This worked for me. Tested with Date and it extracts the right records.
https://www.svenpauwels.com/microsoft-flow/flow-sql-server-connector-how-to-filter-the-date/
Thanks
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogJEEEEEZUZ Where did you dig that up!?!?!! It worked!
For all those in the future that need to do this, here is the correct syntax:
StormDate ge date(2020-01-01T00:00:00Z)
To get 30 days of data, I did the following expression inside the purple box:
addDays(utcNow(),-30)
and so the final thing looks like:
and evaluates to:
If anyone from MS ever reads this, PLEASE update your documentation. PLEASE.
Hi everyone, please consider voting on this 'idea' to fix the implementation of the ODATA spec for date and datetime filtering. I started a ticket with MS and they have told me that it is 'by design' and not on the roadmap to fix. If you would please vote there's a chance they may fix it.
Hello @Ajh
Please, have a look at this thread: https://powerusers.microsoft.com/t5/General-Power-Automate/SQL-Get-Rows-Date-Filter-Between-Two-Date... The solution is based on creating a new calculated column (integer) based on the date, and using it to apply the filter.
Hope it helps!
Ferran
I don't accept that as a solution. OData supports date filters, that Microsoft site I linked states and has an example that it supports date filters but that example doesn't (currently) work (at least not for me, does it work for anyone else?).
Hello @Ajh
I have tested the following flow and it's working fine:
It's an Azure SQL Data table. Which SQL version are you using? Is it on-prem or cloud?
Hope it helps!
Ferran
Thanks Ferran, appreciate you taking the time.
SQL Server 14.0.3049.1, on premise
@Ajh an expression in a filter query must always be inside single quotes. If it doesn't work then it's something else, but you need the single quotes.
Rob
Los Gallardos
@RobElliott I don't see that in Ferran's example, but I have tried that as well:
edit, adding the flow:
Hi @Ajh
I can see from your screenshot you have a StormDate with value as '2020-01-10T00:00:00Z'
Could you please try the below filter using equals?
StormDate eq '2020-01-10T00:00:00Z'
Let me know what results you are getting.
Thanks
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogLooks like it works for equals, but not ge or gt, so maybe it's a bug with the ge/gt operators?
Hi @Ajh
Try these
StormDate gt DateTime'2020-01-10T00:00:00'
StormDate gt DateTime'2020-01-10T00:00:00Z'
Let me know the results.
Thanks
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogI tried DateTime, datetime, and dateTime already. Same failure message.
Hi @Ajh
Ok thanks.
What we don't know is what version of ODATA is using here?
The only alternative option is get all the records with no filter. Then format both dates to yyyyMMddhhmmss format (Basically converting the datetime to a number format) and compare it? That should work.
Thanks
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blog@abm yea I really don't want to do that as a workaround because it could end up being a large data set.
Any ides how to determine what oData version is being used? Any way to get someone from MS to look at this? It seems like a bug.
Hi @Ajh
What version of SQL on-prem are you using? I need to install a developer version and test this. Interesting post and see what I could do? Do you want to compare with date or datetime as well?
Thanks
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogHi @Ajh
Just noticed that from your earlier screenshot the equal operator didn't errored but it didn't returned anything. See below Body value[].
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogHi @Ajh
I did installed a developer edition of SQL server.
Here is my table schema datatype.
Below is my sample data
Power Automate Build Action Steps:
The filter query I used is
Year(StormDate) eq 2020 and month(StormDate) eq 3 and day(StormDate) eq 15
This is my run result
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blog@abm Thanks for testing, but I showed above that eq works fine. Are you able to make ge / gt work?
Hi @Ajh
The equal didn't worked earlier. I did mentioned that in my earlier post. See below body value returns null result.
I did tried now with Greater than (ge) filter query.
This is my test result
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogI apologize, I see the disconnect now. You are separating into year, month, and day integers and filtering on those. That works in all cases that I tried, however while it works it is not an acceptable solution. When querying more than 365 days of data, it will not result in correct data. When filtering on eq '2020-01-01' it works, but when using ge or gt on a single date it does not work.
Hi @Ajh
Thanks for your quick reply.
You mentioned "When filtering on eq '2020-01-01' it works" without splitting the year, month and date?
Thanks
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogThe first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Announcing a new way to share your feedback with the Power Automate Team.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
User | Count |
---|---|
72 | |
27 | |
22 | |
15 | |
13 |
User | Count |
---|---|
141 | |
42 | |
42 | |
34 | |
30 |