cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yakimo
Level: Powered On

date filtering problem

When I try to filter the dataset by Date, I get an error:

"Inner exception: we cannot apply operator < to types Date and DateTimeZone"

What it is for DateTimeZone type? The field dato is datetime sql type

Cannot figure out how to filter my recordset by Date 😞

Any help out there?

 

Thanks

 filterByDate.JPG

10 REPLIES 10
hpkeong
Level 10

Re: date filtering problem

Hi

 

Looking at your syntax:

- SortByColumns(DS, DatePicker.SelectDate >= dato.....

I guess that dato is one of your fieldtitle.

 

Please take note that:

- The SortByColumns function can also be used to sort a table based on one or more columns.

SortByColumns( Table, ColumnName1 [, SortOrder1, ColumnName2, SortOrder2, ... ] )

- The Sort function sorts a table based on a formula.

 Sort( Table, Formula [, SortOrder ] )

 

In this case, you should have used Sort since you have formula inside.

SortByColumns shall use only Column FieldTitle.

 

Please check against this tutorial:

https://powerapps.microsoft.com/en-us/tutorials/function-sort/

 

Hope this helps.

 

Have a nice day.

 

hpkeong
Meneghino
Level 10

Re: date filtering problem

Hi @Yakimo

There seems to be nothing wrong with your syntax.  I suspect that possibly DatePicker1.SelectedDate and dato are being treated as different data types (i.e. one Date and the other DateTimeZone) and that these cannot be compared by >=.

To see if this is correct, try to substitute DatePicker1.SelectedDate with something like Date(2017, 4, 1) and see what that gives.

If not, then try Date(2017,3,1)+Time(12, 0, 0, 0)

If either of the above works then you should force the conversion of DatePicker1.SelectedDate to the type that you need by using the date and time functions.

Please let me know how you get on.

Yakimo
Level: Powered On

Re: date filtering problem

Hi hpkeong

 

Thanks for the answer, unfortunately it didn't help.

Actually, my big problem is filtering, not sorting.

 

I want (what I think shall be straight forward) to show my data table only for a particular day. My data table has a dato column, which is DateTime type (contains time as well). I believe that the right filter condition should be like:

Filter('[dbo].[logbog]',DateOnly(dato) = DatePicker1.SelectedDate)

But I do not know which is the function to truncate time from DateTime (DateOnly(dato))

I tried to convert both dato and DatePicker1.SelectedDate to text and use it in filter, but it doesn't show results - empty output.

I tried to convert to text both - dato and DatePicker1.SelectedDate, but no success.

SortByColumns(Filter('[dbo].[logbog]',Text(DatePicker1.SelectedDate,ShortDate)= Text(dato,ShortDate)), "dato",If(SortDescending1, Ascending,Descending))

 

Any suggestions how to filter my data for one single day only?

Thanks, 

Yakimo

Yakimo
Level: Powered On

Re: date filtering problem

Hi @Meneghino

 

Thanks for you answer.  I made a lot of tests, but to no avail. Here are my observations:

SortByColumns(Filter('[dbo].[logbog]',Date(2017, 4, 1) > dato), "dato",If(SortDescending1, Ascending,Descending))

Error: We cannot apply operator < to types Date and DateTimeZone

(note – my operator is >, not <, as in the message

 

SortByColumns(Filter('[dbo].[logbog]',Date(2017,4,2)+Time(11, 10, 0, 0) >= dato), "dato",If(SortDescending1, Ascending,Descending))

The same error: We cannot apply operator < to types Date and DateTimeZone

 

But there is no error, if I use = (equal) operator instead >

No error even with

SortByColumns(Filter('[dbo].[logbog]',DatePicker1.SelectedDate= dato), "dato",If(SortDescending1, Ascending,Descending))

But no data is displayed.

 

I want (very simple thing) - to show my records from data table only for a particular day. My data table has a dato column, which is DateTime type, i.e. contains date and time. I believe that the right filter condition should be

Filter('[dbo].[logbog]',DateOnly(dato) = DatePicker1.SelectedDate)

But I do not know which is the function to truncate time from DateTime (DateOnly(dato)), so I decided to convert both to text and compare that in the filter like this: 

SortByColumns(Filter('[dbo].[logbog]',Text(DatePicker1.SelectedDate,ShortDate) = Text(dato,ShortDate)), "dato",If(SortDescending1, Ascending,Descending))

Now - no error, only warning: "Part of this filter formula cannot be evaluated remotely due to service limitation..."

But the result is again empty

 

How can I return and display only data for a one particular day from my data table?

I am very confused not able to perform such a simple task...

 

Thanks, 

Yakimo

 

hpkeong
Level 10

Re: date filtering problem

Hi @Yakimo

 

Yes, you are right, it is tricky to trim date and compare.

May I suggest to use LEFT & RIGHT?

Let say

- Dato = 12/03/2017 15:23

You can always use: Left(Dato, 10), or a combination of Right & Left, dependin gon your format.

 

Hope it helps.

hpkeong
Yakimo
Level: Powered On

Re: date filtering problem

Hi @hpkeong@Meneghino

 

I cannot get it right 😞

If I use TextSearchBox1.Text in Logtekst in filter to show everything, and I print ShortText for date conversion of both DatePicker1.SelectedDate and my dato column, I get that the two strings are equal (see the true in blue. (To the left of printed yellow text is the formula text)

1.png

 

But if I change to filter by text of dato and DatePicker1 (which for the previous filtering returned TRUE), I do not get any records back

Filter expression:
SortByColumns(Filter('[dbo].[logbog]',Text(DatePicker1.SelectedDate,ShortDate) = Text(dato,ShortDate)), "dato",If(SortDescending1, Ascending,Descending))

Doesn’t return anything - see the output here:

2.png

 

What am I doing wrong?

Thanks

Yakimo

 

 

Meneghino
Level 10

Re: date filtering problem

Hi @Yakimo

 

With the Text in the second expression, you are having problems linked to delegation:

https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/

This means that it will only query the first 500 records of your data source, and there are no records with today's date in there.

 

You can see that this is the issue if you place the cursor over the blue underlined portion of your formula.

 

I have had similar problems with dates in the past, and the way I have resolved it is to create a calculated integer field in the logbog table in SQL Server with value yyyymmdd, and then you can simply use the following for the filter expression:

Value(Text(DatePicker1.SelectedDate, "yyyymmdd")) >= MyCalculatedDateIntegerField

This should work for sure as a work-around.

 

 Unfortunately PowerApps has a very unclear approach to dates in SQL Server.

 

Please let me know.

Meneghino
Level 10

Re: date filtering problem

Hi @Yakimo

I confirm that a date type of column in Azure SQL DB gives the same type of error you had.  This just goes to confirm that the treatment of dates in PowerApps still leaves a lot to be desired.

I suggest you try the work-around.

 

Capture___.JPG

Highlighted
Yakimo
Level: Powered On

Re: date filtering problem

Hi @Meneghino

 

Yes, you are certainly right, @Meneghino. Suddenly I noticed that if I select 1, 2, 3 or 4 Jan-2016, I can get data displayed on the app for that day. For the 4-Jan - I only get data up to 11:59 am.

After that I went on my SQL server and selected the fist (top) 500 records - and guess what - the last record was 4-Jan 2016, 11:59 am.

SO - the filter works only for the first 500 records - proved.

It's a pity that there is no way to get it working, without introducing another column. 

By the way - in another powerapp I was working - in order to use the dataset, I had to create artificial new text column, because my SQL server table didn't have text column, and without text column it gives error. But fortunately - the error message was descriptive, saying that I need to have a text column...

This with filtering and getting only the first 500 records - it would save me a lot of troubles, if there was kind of hint that the filtered data is not completed.

 

Thanks for the help, Meneghino

Hope this will help others.

 

Regards,

Yakimo

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,924)