date filtering problem

[Yakimo]

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

 

[hpkeong]

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.

[Meneghino]

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]

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]

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]

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.

[Yakimo]

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)

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:

What am I doing wrong?

Thanks

Yakimo

[Meneghino]

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]

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.

[Yakimo]

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