cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

SQL GetRows OData Date Filter

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

SQLWorkOrderFilter

 

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.

 

  • StormDate gt '2020-02-01T16:38:07.0000000Z'
  • StormDate gt 2020-02-01T16:37:41.0000000Z
  • StormDate gt '2020-12-12T11:11:11Z'
  • StormDate gt '2020-01-01T00:00:00Z'
  • StormDate gt '2020-01-01T00:00:00.000Z'
  • StormDate gt '2020-01-01T00:00:00.00Z'
  • StormDate gt 2020-01-01T00:00:00.00Z
  • StormDate gt datetime'2020-01-01T00:00:00.000Z'

They pretty much all end like this:

 

image.png

Here's a successful response where I leave the filter off:

image.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials

View solution in original post

Highlighted

JEEEEEZUZ 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:

image.png

and evaluates to:

image.png

If anyone from MS ever reads this, PLEASE update your documentation. PLEASE. 

View solution in original post

45 REPLIES 45
Highlighted
Super User II
Super User II

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

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
Highlighted

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?). 

Highlighted

Hello @Ajh 

I have tested the following flow and it's working fine:

2020-03-12 18_25_15-Window.png

It's an Azure SQL Data table. Which SQL version are you using? Is it on-prem or cloud?

Hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
Highlighted

Thanks Ferran, appreciate you taking the time.

 

SQL Server 14.0.3049.1, on premise

 

image.pngimage.png

Highlighted

@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

Highlighted

@RobElliott  I don't see that in Ferran's example, but I have tried that as well:

 

image.png

edit, adding the flow:

image.png

Highlighted
Super User III
Super User III

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted

@abm 

Looks like it works for equals, but not ge or gt, so maybe it's a bug with the ge/gt operators?

 

image.png

image.png

 
 

image.png

image.png

Highlighted
Super User III
Super User III

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted

I tried DateTime, datetime, and dateTime already. Same failure message.

Highlighted
Super User III
Super User III

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

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted

@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.

Highlighted
Super User III
Super User III

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted
Super User III
Super User III

Hi @Ajh 

 

Just noticed that from your earlier screenshot the equal operator didn't errored but it didn't returned anything. See below Body value[].

 

image.png



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted
Super User III
Super User III

Hi @Ajh 

 

I did installed a developer edition of SQL server.

 

Here is my table schema datatype.

image.png

 

Below is my sample data

image.png

 

Power Automate Build Action Steps:

 

image.png

 

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

image.png

 

 

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted

@abm Thanks for testing, but I showed above that eq works fine. Are you able to make ge / gt work?

Highlighted
Super User III
Super User III

Hi @Ajh 

 

The equal didn't worked earlier. I did mentioned that in my earlier post. See below body value returns null result.

 

image.png

 

I did tried now with Greater than (ge) filter query.

 

image.png

 

This is my test result

 

image.png



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted

I 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. 

Highlighted
Super User III
Super User III

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (4,616)