Filtering on-prem SQL data source by date

[df]

Consider the following table residing in an on-premise SQL database called DateTable (pulled into powerapps via an on-premise gateway):

Name   DateField

-------   ---------------------------

Item1    2015-01-01 00:00:00

Item2    2017-01-01 00:00:00

When I try to filter this table in Powerapps using the following statement, nothing comes back.

Filter('[dbo].[DateTable]', DateField > DateValue("1/1/2016"))

To debug the issue, I created the following collection:

ClearCollect(DateCollection, {Name: "Item1", DateField: DateValue("1/1/2015")}, {Name: "Item2", DateField: DateValue("1/1/2017")})

The same filter expression applied to this collection works and correctly brings back the one row for 2017:

Filter(DateCollection, DateField > DateValue("1/1/2016"))

Any idea why I can't filter my SQL data source by date?

Thanks!

[rgruian]

The type of your DateField column in SQL is unclear. You are listing the values as 2105-01-01 00:00:00 and such, but are those text values or actual date/time values?

If the former, could you please try this instead:

Filter('[dbo].[DateTable]', DateValue(DateField) > DateValue("1/1/2016"))

[df]

Hi Radu,

Thanks for your response. Here is the table definition:

CREATE TABLE dbo.DateTable (Name varchar(10) PRIMARY KEY, DateField DATETIME)
INSERT INTO dbo.DateTable VALUES ( 'Item1', '2015-01-01')
INSERT INTO dbo.DateTable VALUES ( 'Item2', '2017-01-01')

As you see, the column is defined as DATETIME. So when I use the formula you suggested I get this error:

"Invalid argument type (DateTime). Expecting a Text value instead."

[rgruian · Accepted solution]

This is a bug in PowerApps, which I have just filed and dispatched to the correct area owners. We will address this as soon as possible. In the mean time, if you are blocked, please use one of the following workarounds:

1. If possible, use a DATETIMEOFFSET type for your field instead of DATETIME.

2. Force-inhibit the delegation of the query to the SQL back end, by using non-delegatable constructs. For example:

     Filter('[dbo].[DateTable]', Day(DateField) > 0 && DateField > DateValue("1/1/2016"))

Keep in mind that #2 will run the query locally on up to 500 rows, though. If your data is larger than 500 rows, you will need to go the route of #1 for now until this problem is fixed.

I hope this helps.

[df]

Great, thank you. The dataset is large, so local processing is not an option. Will you post an update here once the bug has been fixed?

Thanks!

[rgruian]

Will do. Thanks for your patience!

[francorg]

Is there a way to filter using a "Today" or "Now" variable in a Flow connecting to SQL Server on-prem?

Like "Orderdate ge Today()"

And what about a way to have it filter only dates greater than the last execution time of the Flow?

Thank you,

Roberto

[LateNightDBA]

Another solution is to add your hours offset to the datetime values before being stored/used for query/filter. This will then equate back to the original date value only.

Example in Hong Kong (GMT+8) I add 8 hours to the datevalue.

Hope that also helps.

Looking forward to the update....

[ericrick]

Is there an update on this getting fixed?   Facing the same situation and it's been a year since mentioned.

[claydevin]

Is there any update on this issue?   We tried the DateTimeOffSet suggestion, but could not get that to work.   We also tried turning the datetime into a number and evaluating that way, but that also is proving challenging.