cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yoshihirok
Level 10

How can I query a datetime by SQL Server - Get Rows with Filter Query ?

I want to get rows from SQL Server with Filter Query.

Just like query ' duedate <= '2018-01-01T00:00:00Z'.

 

Currently, I can get rows from SQL Server with Filter Query like id eq 100

but, I cannot get rows from SQL Server with Filter Query

NG like -- duedate le '2018-01-01T00:00:00Z'

NG like -- duedate le datetimme'2018-01-01T00:00:00Z'

 

I want the Filter Query string to get rows from datetime column equal or less than 2018-01-01T00:00:00Z

 

Regards,

Yoshihiro Kawabata

2 REPLIES 2
Highlighted
Community Support Team
Community Support Team

Re: How can I query a datetime by SQL Server - Get Rows with Filter Query ?

Hi @yoshihirok,

 

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

Do you want to filter rows using OData Query within the Filter Query field of the "Get rows" action?

 

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

  • Add a proper trigger, here I use Flow Button trigger.
  • Add a "Get rows" action, within Filter Query field, type the following formula:
Year(Task_due_date) le 2018 and month(Task_due_date) le 3 and day(Task_due_date) le 15

Note: The Task_due_date column is a datetime type column in my SQL table. On your side, you should type the following formula within the Filter Query field of the "Get rows" action:

Year(duedate) le 2018 and month(duedate) le 1 and day(duedate) le 1

Image reference:1.JPG

The flow works successfully as below:2.JPG

 

 

Please also check and see if the following article thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Flow-Discussion/SQL-Get-Rows-Date-Filter-Between-Two-Dat...

 

More details about OData Query in SharePoint, please check the following article:

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

 

 

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.
yoshihirok
Level 10

Re: How can I query a datetime by SQL Server - Get Rows with Filter Query ?

Hi @v-xida-msft

 

The Filter Query 'Year(Task_due_date) le 2018 and month(Task_due_date) le 3 and day(Task_due_date) le 15' will be work,

But the Filter Query may NOT use the index of the column on SQL Server.

It's too HOLLIABLE.

 

I want a Filter Query by using Index, for improve the SQL Server.

 

Regards,

Yoshihiro Kawabata

 

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 (4,775)