Filter SQL Date field

(0) Share

Report

Posted on by Eric McKinney

173

Flow community

I am trying to do a simple odata filter on a date field called ExecutionDate in Azure DB.

Ultimately I want to make this dynamic but that is prooving to be a unfruitful for me at the moment. So I tried going with something simple like a static date just to see if i was on the right path.

My OData filter is ExecutionDate ge 2017-09-20 ( i have also tried '2017-09-20'). however whenever i run this i get a 502 bad gateway, when i take that filter out it runs wide open and runs fine. I have tried various date filtering tricks but all of them end with a bad gateway. I have other flows that run just fine with with odata filters, but none of them involve dates.

I have seen various posts on how to filter on dates but nothing is turning up concrete.

Categories:

General topics

I have the same question (0)

All responses (9)

Answers (1)

Verified answer

v-yamao-msft on at

Like (4)

Report
Hi emckinney81,

Please try to use the following format to filter the items with SQL Get rows action.

year(birthday) ge 2017 and month(birthday) ge 09 and day(birthday) ge 20

I have tried it on my side, and it is working fine.

Please try it on your side.

When working with date value, we should get the year, month, day separately. Please take this doc for a reference:
https://docs.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests

Best regards,
Mabel Mao

Was this reply helpful? Yes No
Eric McKinney 173 on at

Like (0)

Report

Mabel

I will give that a try thank you.

If that works, that will be a step in the right direction, but how would you go about making that dynamic? For example what if I want to filter for the last 7 days?

Was this reply helpful? Yes No
Eric McKinney 173 on at

Like (0)

Report

Mabel this did work, however, is there a formula that make it dynamic? Or is there a formula to query against sql date fields to make it dynamic, so for example, say I want the last 7 days?

Was this reply helpful? Yes No
moutinhoabreu 170 on at

Like (0)

Report

Thanks, it worked for me too.
Can i kindly ask you where can i find a library of expressions we can use in ODATA filter query.
You used YEAR; MONTH and so on.
It would be helpfull to search for more ecpressions

Appreciated.

Was this reply helpful? Yes No
moutinhoabreu 170 on at

Like (0)

Report

were you able to make it dynamic.
For instace current year ?

Was this reply helpful? Yes No
André Arnaud de Cal... 209 on at

Like (2)

Report

Unfortunately, the verified answer is NOT working. If you need to have all records with a date equal or greater than lets say 2018-12-20, records with a date 2019 and later in the months January until November will be skipped. Only in the December months days from 20 will be used as per the suggestion above. "year(date) ge 2018 and month(date) ge 12 and day(date) ge 20"
I really wonder why the date should be split and Odata cannot just use GE, GT, LE or LT commands on a full datetime variable.
I would suggest to use the next filter:
"(year(date) eq 2018 and month(date) eq 12 and day(date) ge 20) or (year(date) eq 2018 and month(date) gt 12) or (year(date) gt 2018)"

Was this reply helpful? Yes No
dchiu2007 5 on at

Like (0)

Report

To create a dynamic date filter query, you need to use a Compose action. Well explained here: http://benitezhere.blogspot.com/2018/12/sending-a-birthday-email-from-cds-fields-using-flow.html

Was this reply helpful? Yes No
André Arnaud de Cal... 209 on at

Like (1)

Report

Hi dchiu2007,

Unfortunately, the link is not working. It says that the page does not exists. Do you have a correct link?

Was this reply helpful? Yes No
Nagabhushana 3 on at

Like (0)

Report

But y this works. not the direct comparison such as xxxdate gt addDays(utcnow(),-90,'yyyy-MM-dd') and all possible options are not working except for above one.

Was this reply helpful? Yes No