cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
niamhks
Level: Powered On

ODATA query with SQL

Hi all, 

 

I am trying to get rows from a sql database that were created today. The date field in the database is date1.  I can't get all rows and then use a condition because there are over 1000 rows added to this database a day. 

 

I want to use a filter query but am unsure on the syntax to use. Any help would be very appreciated! 

 

Thanks in advance! 

Niamh 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: ODATA query with SQL

Hi @niamhks,

 

How do you want to filter rows of your SQL table with OData query which is filled in Filter Query field of "Get rows" action?

Do you want to filter rows of your SQL table based on date1 field?

 

I suppose that you want to filter rows of your SQL table based on date1 field, is it true? I have created a SQL table on my On-premises SQL Server and the data structure of it as below:13.JPGNote: The date1 field is a date type column in my SQL table.

 

I assume that you want to filter rows of your SQL table whose date1 field is greater than or equal to 2018-02-12. I have made a test on my side and please take a try with the following workaround:14.JPG

Within Filter Query field of "Get rows" action, type the following formula:

year(date1) ge 2018 and month(date1) ge 02 and day(date1) ge 12

The flow works successfully as below:15.JPG

 

When working with date value, we should get year, month and day separately. Please take a try with the following article as a reference:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-...

 

More details about Filter System Query Option, please check the following article:

Filter Dystem Query Option ($filter)

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team
Community Support Team

Re: ODATA query with SQL

Hi @niamhks,

 

How do you want to filter rows of your SQL table with OData query which is filled in Filter Query field of "Get rows" action?

Do you want to filter rows of your SQL table based on date1 field?

 

I suppose that you want to filter rows of your SQL table based on date1 field, is it true? I have created a SQL table on my On-premises SQL Server and the data structure of it as below:13.JPGNote: The date1 field is a date type column in my SQL table.

 

I assume that you want to filter rows of your SQL table whose date1 field is greater than or equal to 2018-02-12. I have made a test on my side and please take a try with the following workaround:14.JPG

Within Filter Query field of "Get rows" action, type the following formula:

year(date1) ge 2018 and month(date1) ge 02 and day(date1) ge 12

The flow works successfully as below:15.JPG

 

When working with date value, we should get year, month and day separately. Please take a try with the following article as a reference:

https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-...

 

More details about Filter System Query Option, please check the following article:

Filter Dystem Query Option ($filter)

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
niamhks
Level: Powered On

Re: ODATA query with SQL

Hi, 

 

Thank you very much for this I have had chance to have a go using this and I am still unsuccessful. the datatype of my date1 column is a datetime do you think this would affect the outcome at all?

 

 

980Panama
Level: Powered On

Re: ODATA query with SQL

I have the same issue with a SQL database using Get Rows.  The suggested solution works fine except when it comes to a month end or year end.  At month end the day(dataitem) gt day(startdate) (where start date is for instance 27 and today's date is 3 and you want to pick up all the data with a date in the sql ttable beteen these dates) this does not pick up lower value dates 1, 2, 3 etc. and the same is true at year end with the month field.  This solution shown does not logically handle these cases.  Does anyone have a solution for this please. 

Gjames
Level: Powered On

Re: ODATA query with SQL

I'm having the same problem. I have 80 records that have been modified in May, but this filter in the SQL Get Rows action returns nothing:

year(LastModified) ge 2019 and month(LastModified) gt 4 and day(LastModified) gt 30

 

and this filter will only return items modified on the 30th April

year(LastModified) ge 2019 and month(LastModified) ge 4 and day(LastModified) ge 30

 

However, this format has been quoted many times by various community support team members as a solution, so I'm really confused about whether this is possible or not - so any clarification would be much appreciated please

@v-micsh-msft 

@v-yamao-msft 

@v-xida-msft 

 

 

Helpful resources

Announcements
firstImage

Microsoft Flow Online Conference

Join us for a FULL day of FREE Microsoft Flow Sessions from some of the best minds in the industry!

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

thirdImage

New Flow Community Board!

Check out the new Microsoft Flow Community Blog Topic Suggestion board!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 47 members 4,300 guests
Recent signins:
Please welcome our newest community members: