filter SQL Date: filter on no date / date zero

[HansHeintz]

Not working:

Filter('[dbo].[asiel_verblijf]';vertrek_datum=0)
Filter('[dbo].[asiel_verblijf]';IsEmpty(vertrek_datum))

Filter('[dbo].[asiel_verblijf]';Day(vertrek_datum)=0)

Filter('[dbo].[asiel_verblijf]';IsBlank(vertrek_datum))

Filter('[dbo].[asiel_verblijf]';Text(vertrek_datum)="")

If a gallery shows a record/item with a blank vetrek_datum:

If I put a label in a gallery Day(Thisitem.vertrek_datum) it does say "0"'

If I put a label in a gallery Text(Thisitem.vertrek_datum) it does say ""' (nothing)

If I put a label in a gallery IsBlank(Thisitem.vertrek_datum) it does say True

What will?

I read messages about date filtering SQL tables and I have to say upfront: changing the SQL database is *not* an option for me.

Filtering on vertrek_datum < 01-01-2000 would also work for me but then the server connection throws errors and I read averywhere that sql date comparing with < and > will not work.

The only filter that does work is if I filter on a specific date and do

Text(vertrek_datum;"dd-mm-yyyy") = "23-02-2017"

vertrek_datum=DateValue("23-02-2017") does not work either

Additional info: 

If I do  ClearCollect(collectSQLDates;ShowColumns('[dbo].[asiel_verblijf]';"vertrek_datum")) it appears dates are in format "d-m-yyyy" when inspecting collectSQLDates

Filter('[dbo].[asiel_verblijf]';IsBlank(Text(vertrek_datum;"[$-nl-NL]d-m-yyyy"))) does not work either

I I do

Filter('[dbo].[asiel_verblijf]';DateDiff(vertrek_datum;Today())>800)
I get a number of hits where vertek_datum is in the 2017s

If I do

Filter('[dbo].[asiel_verblijf]';DateDiff(vertrek_datum;Today())>1000)

I get no hits, while there are records with vetrek_datum=0

[HansHeintz]

The problem is too silly to bother with probably.

I worked around it like this

First I made a subset

;;UpdateContext({KennelRecords:
LastN(Filter('[dbo].[asiel_verblijf]'
;kennel_asiel_id>=_kennel_asiel_id
&& kennel_asiel_id<= _last_kennel_asiel_id
);500)
})

Since records represent stays in an animal shelter and i want to find animals that did not leave yet (vertrek_datum=0) I may hopefully assume that this filter doesn't exclude animals that have been there extremely long.

Now I have a subset of 500 on which some of the filters that failed earlier do work. That absolutely none worked was because when I was using a filter with warning only the first 500 records were considered and none fell in the right category I guess because of the delegation thingie.

Now I can filter the subset in the items of the gallery like this:

Filter(KennelRecords;Day(vertrek_datum)=0) and I get the results I expected

So if I would add this filter to the previous filter I get a delegation warning and nothing shows up, the animals that have no vertrek_datum (date of leaving) are in the bottom part of the table of course.

mmm I cannot rember so much trouble looking for records with no date from my dbase4,clipper,delphi days; strange idea of progress

[v-yutliu-msft]

Hi @HansHeintz ,

Is your problem caused mainly by delegation?

When using large data set, you need to use delegable funtions. Otherwise, the app will not perform well.

Using non-delegable functions, the app can only deal with at most 2000 records.

Here's doc about delegation and delegable funtions in SQL Server:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview

https://docs.microsoft.com/en-us/connectors/sql/

Best regards,

Community Support Team _ Phoebe Liu

[HansHeintz]

Are yout trying to explain to me that it is normal that filtering a table on a date field being empty is a problem?