Filter SQL Date field

[emckinney81]

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. 

[v-yamao-msft · Accepted solution]

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-...

Best regards,
Mabel Mao

[emckinney81]

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?

[emckinney81]

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?

[moutinhoabreu]

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.

[moutinhoabreu]

were you able to make it dynamic. 

For instace current year ?

[AndreAdc]

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)"

[dchiu2007]

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 

[AndreAdc]

Hi dchiu2007,

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

[Nagabhushana]

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.