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

GET SQL ROWS With OData Filter And/OR Query

Hi All, 

 

I am trying to build up a flow using And/OR within Odata filter query to narrow down data coming in from source. I am trying below combination but it going into bad gateway response from server. I know i not writing it in correct format but it would be great if someone can give me some guidance on this. Thanks. 

 

Capture.PNG

 

Expression read as.

formatDateTime(utcNow(), 'yyyy-MM-ddTHH:mm:sszzz')
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: GET SQL ROWS With OData Filter And/OR Query

HI @GaganMehrok,

 

Could you please show a bit more about the Task_due_date column in your SQL table?

Could you please show a full screenshot of your flow's configuration?

 

I have created a SQL table on my side with following code:

Create table dbo.TaskLists1(
  TaskId int not null identity(1,1),
  Task_due_date datetime not null,
  Task_current_state varchar(20) not null,
  Creator varchar(25) not null
)

The Task_due_date column is a datetime type column, the data structure of my SQL table as below:19.JPG

 

 

I think there is something wrong with the OData query that you provided within Filter Query field of "Get rows" action. When working with date value using OData query within Filter Query field of "Get rows" action, 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-...

 

I have made a test on my side and please take a try with the following workaround:20.JPG

Within Filter Query field, type the following formula:

year(Task_due_date) le utcNow(...)​ and month(Task_due_date) le utcNow(...) and day(Task_due_date) lt utcNow(...)​ and Task_current_state eq 'Build' or Task_current_state eq 'Peer Review'

The first utcNow() expression read as:

utcNow('yyyy')

The second utcNow() expression read as:

utcNow('MM')

The third utcNow() expression read as:

utcNow('dd')

 

The flow works successfully as below:21.JPG

 

 

Please check and see if the following article would help you in understanding the above solution that I provided:

https://powerusers.microsoft.com/t5/General-Flow-Discussion/Filter-SQL-Date-field/m-p/59778/highligh...

 

 

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

2 REPLIES 2
Community Support Team
Community Support Team

Re: GET SQL ROWS With OData Filter And/OR Query

HI @GaganMehrok,

 

Could you please show a bit more about the Task_due_date column in your SQL table?

Could you please show a full screenshot of your flow's configuration?

 

I have created a SQL table on my side with following code:

Create table dbo.TaskLists1(
  TaskId int not null identity(1,1),
  Task_due_date datetime not null,
  Task_current_state varchar(20) not null,
  Creator varchar(25) not null
)

The Task_due_date column is a datetime type column, the data structure of my SQL table as below:19.JPG

 

 

I think there is something wrong with the OData query that you provided within Filter Query field of "Get rows" action. When working with date value using OData query within Filter Query field of "Get rows" action, 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-...

 

I have made a test on my side and please take a try with the following workaround:20.JPG

Within Filter Query field, type the following formula:

year(Task_due_date) le utcNow(...)​ and month(Task_due_date) le utcNow(...) and day(Task_due_date) lt utcNow(...)​ and Task_current_state eq 'Build' or Task_current_state eq 'Peer Review'

The first utcNow() expression read as:

utcNow('yyyy')

The second utcNow() expression read as:

utcNow('MM')

The third utcNow() expression read as:

utcNow('dd')

 

The flow works successfully as below:21.JPG

 

 

Please check and see if the following article would help you in understanding the above solution that I provided:

https://powerusers.microsoft.com/t5/General-Flow-Discussion/Filter-SQL-Date-field/m-p/59778/highligh...

 

 

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

DenisStark
Level: Powered On

Re: GET SQL ROWS With OData Filter And/OR Query

Hi, are you sure the query is ok ? I am not getting correct results this way.

In example, I am trying to get records with older dates than a month ago = 2018-09-24:

- I get the record when the date is 2018-04-15 --> all numbers are lt

- I do not get correct record when one of the numbers (month or day) is bigger (2017-10-15 or 2018-07-25)

 

Maybe I did something wrong

 

Thanks for your response.

 

/D

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

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 Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,231)