SQL GetRows OData Date Filter

[Ajh]

I don't understand how to filter on datetime columns in MS Flow / Power Automate. This should be one of the easiest filters to apply... 

This page includes an example with pictures, but when replicated does not work:

WorkOrderCreatedTime gt addDays(utcnow('yyyy-MM-ddTHH:mm:ssZ'),-30)

Spam filter I think is triggering on my links sorry for the funky format:

 https://www.odata.org/documentation/odata-version-2-0/uri-conventions/ 

Note: One of the comments states that it should be surrounded in single quotes, but that also does not appear to work

This page states that the filter criteria for a date/datetime should be surrounded in single quotes and preceded by the word datetime, but this does not appear to work.

DateTime values must be delimited by single quotation marks and preceded by the word datetime, such as datetime'2010-01-25T02:13:40.1374695Z'.

Spam filter I think is triggering on my links sorry for the funky format:

https://docs.microsoft.com/en-us/previous-versions/dynamicsnav-2016/hh169248(v=nav.90) 

Doing year(X) and month(X) etc is not an acceptable solution as it will not work if querying data older than 1 calendar year. 

Here are screen shots of things I've tried that don't work (StormDate is my datetime column). Successful response is included at the bottom to demonstrate the format of the response when I leave this particular filter off.

• StormDate gt '2020-02-01T16:38:07.0000000Z'
• StormDate gt 2020-02-01T16:37:41.0000000Z
• StormDate gt '2020-12-12T11:11:11Z'
• StormDate gt '2020-01-01T00:00:00Z'
• StormDate gt '2020-01-01T00:00:00.000Z'
• StormDate gt '2020-01-01T00:00:00.00Z'
• StormDate gt 2020-01-01T00:00:00.00Z
• StormDate gt datetime'2020-01-01T00:00:00.000Z'

They pretty much all end like this:

Here's a successful response where I leave the filter off:

[fchopo]

Hello @Ajh 

Please, have a look at this thread: https://powerusers.microsoft.com/t5/General-Power-Automate/SQL-Get-Rows-Date-Filter-Between-Two-Date... The solution is based on creating a new calculated column (integer) based on the date, and using it to apply the filter.

Hope it helps!

Ferran

[Ajh]

I don't accept that as a solution. OData supports date filters, that Microsoft site I linked states and has an example that it supports date filters but that example doesn't (currently) work (at least not for me, does it work for anyone else?). 

[fchopo]

Hello @Ajh 

I have tested the following flow and it's working fine:

It's an Azure SQL Data table. Which SQL version are you using? Is it on-prem or cloud?

Hope it helps!

Ferran

[Ajh]

Thanks Ferran, appreciate you taking the time.

SQL Server 14.0.3049.1, on premise

[RobElliott]

@Ajh  an expression in a filter query must always be inside single quotes. If it doesn't work then it's something else, but you need the single quotes.

Rob
Los Gallardos

[Ajh]

@RobElliott  I don't see that in Ferran's example, but I have tried that as well:

edit, adding the flow:

[abm]

Hi @Ajh 

I can see from your screenshot you have a StormDate with value as '2020-01-10T00:00:00Z'

Could you please try the below filter using equals? 

StormDate eq '2020-01-10T00:00:00Z'

Let me know what results you are getting.

Thanks

[Ajh]

@abm 

Looks like it works for equals, but not ge or gt, so maybe it's a bug with the ge/gt operators?

[abm]

Hi @Ajh 

Try these 

StormDate gt DateTime'2020-01-10T00:00:00'

StormDate gt DateTime'2020-01-10T00:00:00Z'

Let me know the results.

Thanks

[Ajh]

I tried DateTime, datetime, and dateTime already. Same failure message.

[abm]

Hi @Ajh 

Ok thanks.

What we don't know is what version of ODATA is using here?

The only alternative option is get all the records with no filter. Then format both dates to yyyyMMddhhmmss format (Basically converting the datetime to a number format) and compare it? That should work.

Thanks

[Ajh]

@abm yea I really don't want to do that as a workaround because it could end up being a large data set. 

Any ides how to determine what oData version is being used? Any way to get someone from MS to look at this? It seems like a bug.

[abm]

Hi @Ajh 

What version of SQL on-prem are you using? I need to install a developer version and test this. Interesting post and see what I could do? Do you want to compare with date or datetime as well?

Thanks

[abm]

Hi @Ajh 

Just noticed that from your earlier screenshot the equal operator didn't errored but it didn't returned anything. See below Body value[].

[abm]

Hi @Ajh 

I did installed a developer edition of SQL server.

Here is my table schema datatype.

Below is my sample data

Power Automate Build Action Steps:

The filter query I used is

Year(StormDate) eq 2020 and month(StormDate) eq 3 and day(StormDate) eq 15

This is my run result

[Ajh]

@abm Thanks for testing, but I showed above that eq works fine. Are you able to make ge / gt work?

[abm]

Hi @Ajh 

The equal didn't worked earlier. I did mentioned that in my earlier post. See below body value returns null result.

I did tried now with Greater than (ge) filter query.

This is my test result

[Ajh]

I apologize, I see the disconnect now. You are separating into year, month, and day integers and filtering on those. That works in all cases that I tried, however while it works it is not an acceptable solution. When querying more than 365 days of data, it will not result in correct data. When filtering on eq '2020-01-01' it works, but when using ge or gt on a single date it does not work. 

[abm]

Hi @Ajh 

Thanks for your quick reply.

You mentioned "When filtering on eq '2020-01-01' it works" without splitting the year, month and date?

Thanks