cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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
Community Support

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.

View solution in original post

4 REPLIES 4
Community Support
Community Support

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.

View solution in original post

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?

 

 

Advocate II
Advocate II

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. 

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Top Solution Authors
Top Kudoed Authors
Users online (8,865)