Bug report: Filter by date with < operator not working in Azure SQL Database or SQL Server

(0) Share

Report

Posted on by Meneghino

6,949

An expression like the following gives an error as shown in the screenshot below:

Filter('[dbo].[MyDates]', CalendarDate <= Date(2000, 1, 1))

Where CalendarDate is a date or a datetime type column.

This has been reported by @Yakimo as well in this post but has received no attention so far.

@Brank has also independently reported the issue.

Replacing the <= by just = works, but is obviously not much use.

Thanks for attending to this.

@v-yamao-msft and/or @v-micsh-msft may be interested in this.

Categories:

Connecting to Data

All responses (10)

Answers (2)

Daryl LaBar 156 Most Valuable Professional on at

Like (0)

Report

Re: Bug report: Filter by date with < operator not working in Azure SQL Database or SQL Server

Converting the DateTime Column in your SQL DB to DateTimeOffset resolves this issue. I've detailed a blog post here on how to do so.
ocdc2008 64 on at

Like (0)

Report

Re: Bug report: Filter by date with < operator not working in Azure SQL Database or SQL Server

I'm having the same issue today in February 2020. The error message changed to be less descriptive. If it simply said you can't use the < operator, at least I'd know what's going on. Instead I get this:

The funny thing is you'd think this fundamental issue would be solved by now. I'm getting really annoyed with PowerApps issues like this.

I made a new thread here:

https://powerusers.microsoft.com/t5/Building-Power-Apps/ClearCollect-Filter-SQLDataSource-DateTime-gt-DateTimeValue/m-p/473761#M142258
Community Power Pla... on at

Like (0)

Report

Re: Bug report: Filter by date with < operator not working in Azure SQL Database or SQL Server

This issue still exists today.

Can we please have an update on whether this limitation is being worked on?

Thank you,
Sam
PhilD 1,009 on at

Like (0)

Report

Re: Bug report: Filter by date with < operator not working in Azure SQL Database or SQL Server

@Megabyte I'm one of those ready to give up 😞

I tried, I created a handful of really awesome, full-featured apps yet only one or two can be used because of fundamental limitations like this.
Megabyte 6 on at

Like (0)

Report

Re: Bug report: Filter by date with < operator not working in Azure SQL Database or SQL Server

Any updates on this problem yet. I'm trying to filter my Azure SQL Database by a date field in the database and I get server errors when I try to use =, <, >. The only operator that actually works in <> not equal too. I've used the work around, by creating a dumby text column on my database containing the date so I can properly filter this field using equal, greater and less then operations. There is another equation workaround that I saw out there, but it only pulls the first 500 records in the table, which is fine for smaller tables, but I need to search 90,000 records in my particular table and exceed the 2000 maximum limit for this workaround regularly. I'm a little surprised there is no fix for this problem yet, since filtering by a date field sounds like a common operation that everyone would want to do.

Can someone from Microsoft please let us know if this is even on the bug list to be fixed? This particular post is over a year old and I am surprised it is not resolved yet, seems like a high priority glitch. I'm actually debating about shelving PowerApps, till a proper solution is found. PowerApps, looks like a great product for small business, but I'm sure lots of other customers gave up on using this product because the workarounds are for seasoned programmers and PowerApps seems to be focused more on inexperienced or junior developers.
Meneghino 6,949 on at

Like (0)

Report

Re: Bug report: Filter by date with < operator not working in Azure SQL Database or SQL Server

Hi @Brank
I would love to know how you do the "app server who is doing collecting all information by doing the SQL Sentences".
When you have time please maybe do a post on this forum giving the basic steps of what you did. If you mention me on the post I will get a link to it by email.
Thanks!
Verified answer

Brank 149 on at

Like (1)

Report

Re: Bug report: Filter by date with < operator not working in Azure SQL Database or SQL Server

I was using the type "DateTime" in SQLServer but I just see that it doesn't work well the compare so I had to change it to "Date" type in SQL Server.

Whatever I just change all the logic for collecting data and now I am using an app server who is doing collecting all information by doing the SQL Sentences and return the objects to the powerapps. With this I just quit all the limits that powerapps gives and all those strange things that is doing.
Verified answer

v-micsh-msft on at

Like (0)

Report

Re: Bug report: Filter by date with < operator not working in Azure SQL Database or SQL Server

Hi @Meneghino,

Apologize for the late response.

This seems to be a current limitation:

I will help to collect this as a feedback.

Thank you for reporting this.

Best regards,

Michael
Meneghino 6,949 on at

Like (0)

Report

Re: Bug report: Filter by date with < operator not working in Azure SQL Database or SQL Server

Hi @Brank
My CalendarDate column is of T-SQL date type, and I cannot get your expression to work. I have also tried with T-SQL datetime data type and I get the same error. Finally I tried with datetimeoffset data type and it worked.
What data type is your Fecha_alta column?
In any case @v-yamao-msft and @v-micsh-msft, the expression should work with all date types of columns. I would be very grateful if you could follow up.
Thanks.
Brank 149 on at

Like (0)

Report
Re: Bug report: Filter by date with < operator not working in Azure SQL Database or SQL Server

It seems that Powerapps is recognizing CalendarDate field in database as a DateTimeZone, I tried the following things:

- Convert the type of CalendarDate from "DateTimeZone" to "Date" with:
DateValue(Text(CalendarDate))
But it seems is not doing well the compare.
- Convert the type of the Date to DateTime with:
DateTimeValue(Text(Date(2000, 1, 1)))
it seems that is doing well now the compare but in my case is not returning all rows I expected (don't know why). In my SQL Server doing the sentence:

SELECT * FROM [dbo].[Dispositivos] WHERE Fecha_alta >= '01/04/2016' AND Fecha_alta <= '04/04/2017'
returns 175 rows and powerapps with the sentence:
Filter('[dbo].[Dispositivos]'; DateTimeValue(Text(DatePicker1.SelectedDate)) <= Fecha_alta && DateTimeValue(Text(DatePicker1_1.SelectedDate)) >= Fecha_alta)
Just return 119 rows..

I need a solution for this issue as soon as possible due to I am developing an application in powerapps for a client.

EDIT: My bad, the second thing I did seems to go well. I thought it was returning less values because Powerapps did something bad there but was me who did bad the query in SQL Server because of the format of the date I gave.

You can try @Meneghino if my second solution goes well for you.

Regards,