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

Like (0) Share

Report

Posted on 17 May 2018 04:52:25 by yoshihirok

1,724

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

Categories:

General Questions

I have the same question (0)

All responses (2)

Answers (0)

yoshihirok 1,724 on 21 May 2018 at 04:00:49

Like (0)

Report

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

Was this reply helpful? Yes No
v-xida-msft on 21 May 2018 at 01:46:52

Like (3)

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

The flow works successfully as below:

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-Dates/td-p/71793

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-rest-requests

Best regards,

Kris

Was this reply helpful? Yes No